结果包含多于一行 Error 1172 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2242695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Result consisted of more than one row Error 1172 mysql
提问by ryn6
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!
非常感谢任何解决方案和建议！
回答by MartW
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
回答by Martin
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.
这将导致将 mlist 选择到 itemcode 1 的 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 值的条目，那么您需要决定应如何处理。也许取最高值，或最低值，或任何值。或者这可能是数据中的错误。
回答by Matt Dawdy
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 子句中是否还需要其他内容？
回答by fanxiong
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.
所以你应该检查一下。我以前打过这个。
回答by Ataboy Josef
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 错误 1172 - 结果包含多于一行
Thank you.
谢谢你。
