错误码: 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条就会报你那样的错)
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;
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上是不可以的
这个语法在mysql上是不可以的
#6
只有这两个进行赋值了,其它的赋值只是用
SET $T_FDate='0000-00-00';这个应该没问题。第1个是SUM求和,应该不会出现两条记录。
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;
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
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;
测试时候加--的执行时候没有问题,加#的执行有问题,为什么呢?
--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、 /**/
1、-- ...
2、 /**/
#14
加#的执行有问题,不是注释,那肯定有错了哦
#15
MySQL 中不支持 if exists (select ... 这种啊。
一般是先 select count(*) into v_icnt from ...然后再判断 if v_icnt>0 ...
一般是先 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
看看结果如何
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 ;
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
#1
你把一个有多行结果的查询赋值给一个变量吧?
#2
那应该你过程中有赋给变量值时,符合条件的记录多于1条,如
declare v_i int;
select field_a into v_i from tb_name where id=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;
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上是不可以的
这个语法在mysql上是不可以的
#6
只有这两个进行赋值了,其它的赋值只是用
SET $T_FDate='0000-00-00';这个应该没问题。第1个是SUM求和,应该不会出现两条记录。
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;
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
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;
测试时候加--的执行时候没有问题,加#的执行有问题,为什么呢?
--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、 /**/
1、-- ...
2、 /**/
#14
加#的执行有问题,不是注释,那肯定有错了哦
#15
MySQL 中不支持 if exists (select ... 这种啊。
一般是先 select count(*) into v_icnt from ...然后再判断 if v_icnt>0 ...
一般是先 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
看看结果如何
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 ;
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