代码之家 › 专栏 › 技术社区 › True Soft

MySQL使用用户变量从视图中选择-意外结果

user-variables mysql

0

True Soft · 技术社区 · 15 年前

product_oper 我有一些我收到的产品(当 id_dest 是1)和我出售(当 id_src 是1)。桌子 product_doc 包含操作发生的日期。

CREATE TABLE product_doc (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  doc_date date NOT NULL,
  doc_no char(16) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO product_doc (id,doc_date,doc_no) VALUES 
 (1,'2009-10-07','1'),
 (2,'2009-10-14','2'),
 (3,'2009-10-28','4'),
 (4,'2009-10-21','3');

CREATE TABLE product_oper (
  id bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  id_document bigint(20) unsigned NOT NULL,
  prod_id bigint(12) unsigned NOT NULL DEFAULT '0',
  prod_quant decimal(16,4) NOT NULL DEFAULT '1.0000',
  prod_value decimal(18,2) NOT NULL DEFAULT '0.00',
  id_dest bigint(20) unsigned NOT NULL,
  id_src bigint(20) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO product_oper (id,id_document,prod_id,prod_quant,prod_value,id_dest,id_src) 
  VALUES 
  (10,1,1,'2.0000', '5.00',1,0),
  (11,3,1,'0.5000', '1.20',0,1),
  (12,1,2,'3.0000','26.14',1,0),
  (13,2,2,'0.5000','10.20',0,1),
  (14,3,2,'0.3000', '2.60',0,1),
  (15,4,2,'1.0000', '0.40',1,0);

CREATE VIEW product_oper_view AS
 SELECT product_oper.*, product_doc.doc_date AS doc_date, product_doc.doc_no AS doc_no
 FROM product_oper JOIN product_doc ON product_oper.id_document = product_doc.id
 WHERE 1;

现在我想看看单个产品的操作,以及特定日期的数量和价值。

SET @amount=0.000, @balance=0.00;

SELECT product_oper_view.*,
  IF(id_dest<>0, prod_quant, NULL) AS q_in,
  IF(id_dest<>0, prod_value, NULL) AS v_in,
  IF(id_src<>0, prod_quant, NULL) AS q_out,
  IF(id_src<>0, prod_value, NULL) AS v_out,
  @amount:=@amount + IF(id_dest<>0, 1, -1)*prod_quant AS q_amount,
  @balance:=@balance + IF(id_dest<>0, 1, -1)*prod_value AS v_balance
FROM product_oper_view 
WHERE prod_id=2 AND (id_dest=1 OR id_src=1)
ORDER BY doc_date;

我得到的结果很奇怪:

id, id_ prod_ prod_  id_ id_    doc_date,   q_in,   v_in,                 q_      v_
   doc, quant,value,dest,src,                              q_out, v_out, amount,  balance
12, 1, 3.0000, 26.14, 1,  0, '2009-10-07', 3.0000, 26.14,  NULL ,  NULL,  3.000,  26.14
13, 2, 0.5000, 10.20, 0,  1, '2009-10-14',  NULL ,  NULL, 0.5000, 10.20,  2.500,  15.94
15, 4, 1.0000,  0.40, 1,  0, '2009-10-21', 1.0000,  0.40,  NULL ,  NULL,  3.200,  13.74
14, 3, 0.3000,  2.60, 0,  1, '2009-10-28',  NULL ,  NULL, 0.3000,  2.60,  2.200,  13.34

金额从零开始,
在第1行:+3=>3(好的)
第2行:-0.5=>2.5(正常)
(???)
2.2 (???)

ORDER BY 子句执行语句时,它会查看id:请参阅id为4的文档是之前

我是做错了什么,还是MySQL的一个bug?

1 回复 | 直到 15 年前

1

0

Stefan Gehrig 15 年前

如果我不是完全错的话 ORDER -操作是准备结果集时最后要做的事情之一。因此,在对结果进行排序之前,必须先进行计算。避免此问题的正确方法应该是使用子选择:

SET @amount=0.000, @balance=0.00;

SELECT p.*,
    @amount:=@amount + IF(p.id_dest <> 0, 1, -1) * p.prod_quant AS q_amount,
    @balance:=@balance + IF(p.id_dest <> 0, 1, -1) * p.prod_value AS v_balance
FROM (
    SELECT product_oper_view.*,
        IF(product_oper_view.id_dest <> 0, product_oper_view.prod_quant, NULL) AS q_in,
        IF(product_oper_view.id_dest <> 0, product_oper_view.prod_value, NULL) AS v_in,
        IF(product_oper_view.id_src <> 0, product_oper_view.prod_quant, NULL) AS q_out,
        IF(product_oper_view.id_src <> 0, product_oper_view.prod_value, NULL) AS v_out
    FROM product_oper_view 
    WHERE product_oper_view.prod_id = 2 
        AND (product_oper_view.id_dest = 1 OR product_oper_view.id_src = 1)
    ORDER BY product_oper_view.doc_date
) AS p