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

我们可以在Mysql存储过程的循环中使用Select语句吗？
为什么密码错了

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//

最佳答案

试试这个：

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 ;