mysql - 结果包含多行错误 1172 mysql

Question

你好，我在使用这个存储过程时遇到了困难。我收到错误:结果由多行组成。

这是我的存储过程:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dss`.`COSTRET` $$
CREATE DEFINER=`dwadmin`@`192.168.%.%` PROCEDURE `COSTRET`( TDATE DATE)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ls_id VARCHAR(8);
    DECLARE ld_cost DECIMAL(10,4);
      DECLARE ld_retail DECIMAL(10,4);
    DECLARE cur1 CURSOR FOR SELECT DISTINCT `id` FROM `prod_performance` WHERE `psc_week` = TDATE;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  -- Get the Cost
  CREATE TEMPORARY TABLE IF NOT EXISTS `prod_itemcost`
    SELECT DISTINCTROW `itemcode` ID, `mlist` COST
    FROM (SELECT `itemcode`, `pceffdate`, `mlist`
        FROM `purchcost` a
        where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
        AND z.`pceffdate` <= TDATE)) tb
    ORDER BY `itemcode`;

    OPEN cur1;
    REPEAT
      FETCH cur1 INTO ls_id;
      IF NOT done THEN
            SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;

        UPDATE LOW_PRIORITY `prod_performance` SET `current_cost` = ld_cost WHERE `psc_week` = TDATE and `id` = ls_id;
      END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;

   -- Destroy Temporary Tables
   DROP TEMPORARY TABLES IF EXISTS `prod_itemcost`;
END $$

DELIMITER ;

非常感谢任何解决方案和建议!

Answer 1

我想说问题就在这里:

SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;

并由此返回不止一行。如何解决它取决于您的要求。例如，多行的存在是否意味着数据库需要一些清理？或者您应该取“成本”的第一个值，还是 id = ls_id 的所有“成本”的总和？

编辑:

您的 INTO 子句试图将多行写入单个变量。看看你的 SQL，我想说的根本问题是，你最初的查询是为了拉回每个 ID 的最新成本，但由于 pceffdate 的重复而受到阻碍。如果是这种情况，这个 SQL :

SELECT DISTINCTROW `itemcode` ID, `mlist` COST
    FROM (SELECT `itemcode`, `pceffdate`, `mlist`
        FROM `purchcost` a
        where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
        AND z.`pceffdate` <= TDATE)) tb

将返回比这更多的行:

SELECT DISTINCTROW `itemcode` ID
    FROM (SELECT `itemcode`, `pceffdate`, `mlist`
        FROM `purchcost` a
        where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
        AND z.`pceffdate` <= TDATE)) tb