结果包含多行错误1172 mysql

Hello im having a hard time with this stored procedure. im getting the error: Result consisted of more than one row.

你好我很难用这个存储过程。我得到错误：结果由多行组成。

here is my stored procedure:

这是我的存储过程：

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 ;

Any solutions and recommendations are much appreciated!

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

5 个解决方案

#1

I'd say the problem is here :

我想问题就在这里：

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

and caused by this returning more than one row. How you solve it depends on your requirements. Does the existence of multiple rows imply the database is in need of some cleaning, for example? Or should you be taking the first value of 'cost', or perhaps the sum of all 'cost' for id = ls_id?

并且由此返回多行。你如何解决它取决于你的要求。多行的存在是否意味着数据库需要进行一些清理，例如？或者你应该采用'cost'的第一个值，或者id = ls_id的所有'cost'的总和？

Edit :

编辑：

Your INTO clause is attempting to write multiple rows to a single variable. Looking at your SQL, I'd say the underlying problem is that your initial query to pull back just the latest cost for each ID is being hamstrung by duplicates of pceffdate. If this is the case, this SQL :

您的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

will return more rows than just this :

将返回更多行而不仅仅是：

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

#2

This line

这条线

SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
    AND z.`pceffdate` <= TDATE

has got to be the problem. It must be returning more than 1 row. So, the DBMS is trying to set multiple values to the same thing, which of course it cannot do.

必须成为问题。它必须返回超过1行。因此，DBMS正在尝试将多个值设置为相同的东西，这当然是不可能的。

Do you need something else in your WHERE clause there?

你的WHERE子句中还需要其他东西吗？

#3

The problem is that

问题是

SELECT DISTINCTROW `itemcode` ID, `mlist` COST

could store multiple costs against each ID, and so

可以为每个ID存储多个成本，等等

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

could return multiple rows for each id.

可以为每个id返回多行。

For example, if purchcost contained the following:

例如，如果purchcost包含以下内容：

itemcode   mlist   pceffdate
1          10.99   10-apr-2009
1          11.99   10-apr-2009
1           9.99   09-apr-2009

Then temporary table prod_itemcost would contain:

然后临时表prod_itemcost将包含：

itemcode   mlist
1          10.99
1          11.99

These both being values that were in effect on the most recent pceffdate for that itemcode.

这两个值都是对该商品代码的最新pceffdate有效的值。

This would then cause a problem with selecting mlist into ld_cost for itemcode 1 because there are two matching values, and the scalar ld_cost can only hold one.

这将导致在项目代码1中选择mlist到ld_cost时出现问题，因为有两个匹配值，而标量ld_cost只能容纳一个。

You really need to look at the data in purchcost. If it is possible for 1 item to have more than one entry with different mlist values for the same date/datetime, then you need to decide how that should be handled. Perhaps take the highest value, or the lowest value, or any value. Or perhaps this is an error in the data.

你真的需要看看purchcost中的数据。如果1个项目可能有多个条目具有相同日期/日期时间的不同mlist值，那么您需要决定应该如何处理。也许取最高值，或最低值，或任何值。或者这可能是数据中的错误。

#4

Here is the correct solution. Take a look at my answer to this question MySQL Error 1172 - Result consisted of more than one row

这是正确的解决方案。看看我对这个问题的回答MySQL Error 1172 - 结果由多行组成

Thank you.

谢谢。

#5

There is another possibility, that is your parameter "TDATE" same as table field name in uppercase or lowercase or mixed. such as 'tdate', 'tDate', 'TDATE'.

还有另一种可能性，就是您的参数“TDATE”与大写或小写或混合的表字段名称相同。例如'tdate'，'tDate'，'TDATE'。

so you should check that. I hit this before.

所以你应该检查一下。我以前打过这个。

#1