mysql循环select,Mysql存储过程中循环内的Select语句

Can we use Select statement inside a loop in a Mysql Stored Procedure?

why is the code wrong

create procedure AbsentReportproc (INOUT fromdate DATETIME, INOUT todate DATETIME)

as

begin

DECLARE startdate DATE;

DECLARE enddate DATE;

DECLARE nofdays INT;

DECLARE counter INT;

DECLARE countdate DATE;

startdate=fromdate;

enddate=todate;

countdate=fromdate;

nofdays=DATEDIFF(DAY,startdate,endate);

counter=1;

while counter<=noofdays

loop

select CARDNO from test_prefixmaster

where CARDNO not in ( select CARDNO from test_prefixtransactions where Date(S_DateTime)=countdate)

set countdate=countdate+1;

set counter=counter+1;

end loop;

end//

解决方案

Try this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `AbsentReportproc`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AbsentReportproc`(IN _fromdate DATETIME, IN _todate DATETIME)

BEGIN

CREATE TEMPORARY TABLE daterange (dte DATE);

SET @counter := -1;

WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO

INSERT daterange VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY));

END WHILE;

SELECT tp.cardno, tp.EMPCODE, tp.DEPARTMENT, GROUP_CONCAT(d.dte) Absentddate, COUNT(tp.cardno) Totalnoofabsentdates

FROM test_prefixmaster tp JOIN daterange d

LEFT JOIN test_prefixtransactions tpt ON tp.cardno = tpt.CARDNO AND DATE(S_DateTime) = d.dte

WHERE tpt.CARDNO IS NULL

GROUP BY tp.cardno;

DROP TABLE daterange;

END$$

DELIMITER ;