Result consisted of more than one row

存储过程执行出现如下错误提示：
错误码: 1172
Result consisted of more than one row
是怎么回事？

19 个解决方案

#1

你把一个有多行结果的查询赋值给一个变量吧？

#2

那应该你过程中有赋给变量值时，符合条件的记录多于1条，如

declare v_i int;

select field_a into v_i from tb_name where id=2; (这里面符合条件的记录多于1条就会报你那样的错）

#3

结果集返回超过一条记录，你用子查询？贴代码

#4

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;

#5

 IF EXISTS(

这个语法在mysql上是不可以的

#6

只有这两个进行赋值了，其它的赋值只是用
SET $T_FDate='0000-00-00';这个应该没问题。第1个是SUM求和，应该不会出现两条记录。

#7

是在SP中？

#8

IF EXISTS( 可以用，系统认识。

#9

修改下：
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; 

#10

WINDOWS XP SP2。修改的程式我试一下

#11

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

#12

        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;
测试时候加－－的执行时候没有问题，加#的执行有问题，为什么呢？

#13

mysql里面的注释为下面2种形式：

1、-- ...
2、 /**/

#14

加#的执行有问题，不是注释，那肯定有错了哦

#15

MySQL 中不支持 if exists (select ... 这种啊。

一般是先 select count(*) into v_icnt from ...然后再判断 if v_icnt>0 ...

#16

不知道你的完整代码，不过在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
看看结果如何

#17

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 ;

#18

我把所有的if exists (select ... ）这种形式改正过来，再测试一下了

#19

我的是5.0.41

#20

• Result consisted of more than one row 错误的解决
• MYSQL存储过程出现Result consisted of more than one row 错误的解决方法