Result consisted of more than one row

lacasadeco 2009-08-26 01:50:12
存储过程执行出现如下错误提示：
错误码: 1172
Result consisted of more than one row
是怎么回事？
...全文
418 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
我的是5.0.41
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
我把所有的if exists (select ... ）这种形式改正过来，再测试一下了
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
DELIMITER $$
DROP PROCEDURE IF EXISTS `wincodb`.`P_GetBosCombinedBegin`$$
CREATE DEFINER=`root`@`%` PROCEDURE `P_GetBosCombinedBegin`($M_FDate VARCHAR(45),$M_TDate VARCHAR(45))
BEGIN
DECLARE $T_FWorkNo,$T_FCustomerName,$T_FDeliveryDate VARCHAR(45) CHARACTER SET utf8;
DECLARE $T_FDate DATE;
DECLARE $T_FName VARCHAR(125) CHARACTER SET utf8;
DECLARE $T_FQty,$T_FYSSL,$T_FZDSS,$T_FJGQty,$T_FBPSL,$LastQty DECIMAL(28,10);
DECLARE $j,$LastPC INT;
DECLARE $FQZ,$FYS,$FGY,$FKZ,$FBZ,$FBH,$FCK,$FMK TINYINT;
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FBillNo,FCustomerName,FName,FYSSL,FZDSS,FJGQty,FDeliveryDate,FQZ,FYS,FGY,FKZ,FBZ,FBH,FCK,FMK
FROM WincoBosCombinedPmc
WHERE(FDate>=$M_FDate)AND(FDate<=$M_TDate)
UNION
SELECT DISTINCT A.FBillNo AS FBillNo,A.FCustomerName,A.FName,A.FYSSL,A.FZDSS,A.FJGQty,A.FDeliveryDate,A.FQZ,A.FYS,A.FGY,A.FKZ,A.FBZ,A.FBH,A.FCK,A.FMK
FROM WincoBosCombinedPmc A INNER JOIN WincoBosCombinedPmcEntry B
ON A.FBillNo=B.FWorkNo
WHERE(B.FDate>=$M_FDate)AND(B.FDate<=$M_TDate)
ORDER BY FBillNo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;
DROP TABLE IF EXISTS t_GetBeginList;
CREATE TABLE t_GetBeginList(
FBillNo VARCHAR(45) NULL,
FCustomerName VARCHAR(45) NULL,
FName VARCHAR(125) NULL,
FGX VARCHAR(45) NULL,
FYSSL DECIMAL(28,10) NULL,
FQty DECIMAL(28,10) NULL,
FDeliveryDate VARCHAR(45) NULL,
FDate DATE DEFAULT '0000-00-00'
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
SET $j=0;
SET $T_FYSSL=0;
SET $T_FZDSS=0;
SET $T_FJGQty=0;
OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FWorkNo,$T_FCustomerName,$T_FName,$T_FYSSL,$T_FZDSS,$T_FJGQty,$T_FDeliveryDate,$FQZ,$FYS,$FGY,$FKZ,$FBZ,$FBH,$FCK,$FMK;
IF NOT $done THEN
#最后一道工序
IF $FQZ=1 THEN
SET $LastPC=1;
SET $LastQty=$T_FZDSS;
END IF;
IF $FYS=1 THEN
SET $LastPC=2;
SET $LastQty=$T_FZDSS;
END IF;
IF $FGY=1 THEN
SET $LastPC=3;
SET $LastQty=$T_FZDSS;
END IF;
IF $FMK=1 THEN
SET $LastPC=8;
SET $LastQty=$T_FJGQty;
END IF;
#工序在生产
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)THEN
SELECT SUM(FBPSL) INTO $T_FBPSL FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
ELSE
SET $T_FBPSL=0;
END IF;
#判断最后工序有无销单
IF $T_FBPSL=0 THEN
LABLE1:WHILE $j<=2 DO
IF $FQZ=1 AND NOT EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID=2)THEN
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待切',$T_FZDSS,$T_FZDSS,$T_FDeliveryDate,$T_FDate);
LEAVE LABLE1;
END IF;
IF $FYS=1 AND NOT EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=2 AND FMethodID=2)THEN
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=2 AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=2 AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待印',$T_FZDSS,$T_FZDSS,$T_FDeliveryDate,$T_FDate);
LEAVE LABLE1;
END IF;
IF $FGY=1 AND NOT EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=3 AND FMethodID=2)THEN
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=3 AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=3 AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待油',$T_FZDSS,$T_FZDSS,$T_FDeliveryDate,$T_FDate);
LEAVE LABLE1;
END IF;
SET $j=$j+1;
END WHILE LABLE1;
ELSEIF $T_FBPSL<$LastQty THEN
IF $LastPC=1 THEN
SET $T_FQty=0;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)THEN
SELECT SUM(FBPSL) INTO $T_FQty FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
END IF;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待切',$T_FZDSS,$T_FZDSS-$T_FQty,$T_FDeliveryDate,$T_FDate);
END IF;
IF $LastPC=2 THEN
SET $T_FQty=0;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)THEN
SELECT SUM(FBPSL) INTO $T_FQty FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
END IF;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待印',$T_FZDSS,$T_FZDSS-$T_FQty,$T_FDeliveryDate,$T_FDate);
END IF;
IF $LastPC=3 THEN
SET $T_FQty=0;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)THEN
SELECT SUM(FBPSL) INTO $T_FQty FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
END IF;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待油',$T_FZDSS,$T_FZDSS-$T_FQty,$T_FDeliveryDate,$T_FDate);
END IF;
IF $LastPC=8 THEN
SET $T_FQty=0;
IF (SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)IS NOT NULL THEN
SELECT SUM(FBPSL) INTO $T_FQty FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
END IF;
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待粘',$T_FJGQty,$T_FJGQty-$T_FQty,$T_FDeliveryDate,$T_FDate);
END IF;
END IF;
END IF;
UNTIL $done END REPEAT;
CLOSE PMC_CH;
SELECT * FROM t_GetBeginList ORDER BY FDate,FBillNo;
SELECT COUNT(FBillNo)FROM t_GetBeginList;
DROP TABLE t_GetBeginList;
END$$
DELIMITER ;
WWWWA 2009-08-26
  • 打赏
  • 举报
 回复
不知道你的完整代码，不过在MYSQL(5.1.32)中支持
if exists (select ... ）这种形式

DECLARE ee VARCHAR(10);
IF EXISTS(SELECT xm FROM jzg1 WHERE bh='101068') THEN
SET ee=123;
SELECT ee;

其中： bh='101068'有16条记录

直接运行
SELECT COUNT(FDate) INTO $v_num FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID <2 GROUP BY FDate
看看结果如何
ACMAIN_CHM 2009-08-26
  • 打赏
  • 举报
 回复
MySQL 中不支持 if exists (select ... 这种啊。

一般是先 select count(*) into v_icnt from ...然后再判断 if v_icnt>0 ...
vinsonshen 2009-08-26
  • 打赏
  • 举报
 回复
加#的执行有问题，不是注释，那肯定有错了哦
vinsonshen 2009-08-26
  • 打赏
  • 举报
 回复
mysql里面的注释为下面2种形式：

1、-- ...
2、 /**/
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
IF $FQZ=1 AND NOT EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID=2)THEN
--IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2)THEN
--SELECT MIN(DISTINCT FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
--WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2
--GROUP BY FDate;
--ELSE
--SET $T_FDate='0000-00-00';
--END IF;
SET $v_num=0;
#SELECT COUNT(FDate) INTO $v_num FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2 GROUP BY FDate;
#IF $v_num>0 THEN
#SELECT DISTINCT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
#WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2
#GROUP BY FDate;
#ELSE
#SET $T_FDate='0000-00-00';
#END IF;
INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待切',$T_FZDSS,$T_FZDSS,$T_FDeliveryDate,$T_FDate);
LEAVE LABLE1;
END IF;
测试时候加－－的执行时候没有问题，加#的执行有问题，为什么呢？
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
SELECT count(FWorkNo) into v_num FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID <2)
if v_num>0 then
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID <2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
这个程式中仍不能查询的FDATE的最小值有两条记录，然后赋值给$T_FDate
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
WINDOWS XP SP2。修改的程式我试一下
vinsonshen 2009-08-26
  • 打赏
  • 举报
 回复
修改下：
1、 #工序在生产
declare v_num int;
SELECT count(FWorkNo) into v_num FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2);
if v_num>0 then
SELECT SUM(FBPSL) INTO $T_FBPSL FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
ELSE
SET $T_FBPSL=0;
END IF;
2、
SELECT count(FWorkNo) into v_num FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID <2)
if v_num>0 then
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID <2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
IF EXISTS( 可以用，系统认识。
WWWWA 2009-08-26
  • 打赏
  • 举报
 回复
是在SP中？
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
只有这两个进行赋值了，其它的赋值只是用
SET $T_FDate='0000-00-00';这个应该没问题。第1个是SUM求和，应该不会出现两条记录。
vinsonshen 2009-08-26
  • 打赏
  • 举报
 回复
IF EXISTS(

这个语法在mysql上是不可以的
lacasadeco 2009-08-26
  • 打赏
  • 举报
 回复
1、 #工序在生产
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)THEN
SELECT SUM(FBPSL) INTO $T_FBPSL FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
GROUP BY FWorkNo,FOrder,FMethodID;
ELSE
SET $T_FBPSL=0;
END IF;
2、
IF EXISTS(SELECT FWorkNo FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2)THEN
SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry
WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2
GROUP BY FDate;
ELSE
SET $T_FDate='0000-00-00';
END IF;
wwwwb 2009-08-26
  • 打赏
  • 举报
 回复
结果集返回超过一条记录，你用子查询？贴代码
vinsonshen 2009-08-26
  • 打赏
  • 举报
 回复
那应该你过程中有赋给变量值时，符合条件的记录多于1条，如

declare v_i int;

select field_a into v_i from tb_name where id=2; (这里面符合条件的记录多于1条就会报你那样的错）
denniswwh 2009-08-26
  • 打赏
  • 举报
 回复
你把一个有多行结果的查询赋值给一个变量吧？
Mysql Update批量更新的几种方式
通常情况下，我们会使用以下SQL语句来更新字段值： UPDATE mytable SET myfield='value' WHERE other_field='other_value'; 但是，如果你想更新多行数据，并且每行记录的各字段值都是各不一样，你会怎么办呢？刚开始你可能会想到使用循环执行多条UPDATE语句的方式，就像以下的python程序示例： for x in xrange(10): sql = ''' UPDATE mytable SET myfield='value' WHERE other_field='other_value'; ''' 这种方法并没有什么任何错误，
Mysql报错：1172 - Result consisted of more than one row
在执行写好的存储过程中出现Result consisted of more than one row的报错， 之前编写存储过程没有报错，检查发现是由于其中的一条语句 select f_id into id from t_app_service中的f_id有多个输出值导致的，加上条件语句进行筛选后输出即可。 ...
[Err] 1172 - Result consisted of more than one row
1 错误描写叙述 [Err] 1172 - Result consisted of more than one row Procedure execution failed 1172 - Result consisted of more than one row 2 错误原因 存储过程里。将查询的结果插入到一个暂时表中，可是查询的数据条数超过一条，导致报错...
mysql存储过程出现--1172 - Result consisted of more than one row错误的解决方法
存储过程出现–1172 - Result consisted of more than one row错误的解决方法一般这种情况的发生是因为存储过程返回结果含有多条记录，但是存储过程中函数并没有将结果进行遍历依次输出，所以就导致报错，而接收的是一个单独的对象，而不是一个以对象而行程的列表来接收结果，所以会导致结果不匹配情况发生。
mysql数学函数存储过程 Result consisted of more than one row
存储过程发生 Result consisted of more than one row
MySQL

56,678

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章