BEGINDECLARE aa VARCHAR(50);
DECLARE flag INT;
declare cur1 CURSOR for select `date` from sp_work GROUP BY `date`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
SET flag=0;DROP TABLE IF EXISTS `temp_work`;
CREATE TEMPORARY TABLE temp_work (
today VARCHAR(10),
total INTEGER ,
status1 INT,
status2 INT,
status3 INT,
status4 INT,
status5 INT
) ENGINE=MEMORY ;
INSERT INTO temp_work(today,total) SELECT `date`,count(*) from sp_work where companyno = company_no GROUP BY `date` ORDER BY `date` desc LIMIT firstRow,listRow;OPEN cur1;
REPEAT
FETCH cur1 INTO aa;
UPDATE temp_work SET status1 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=1 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status2 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=2 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status3 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=3 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status4 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=4 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status5 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=5 and companyno = company_no) WHERE today=aa;
UNTIL flagEND REPEAT;
CLOSE cur1;
SELECT * from temp_work;
END
DECLARE flag INT;
declare cur1 CURSOR for select `date` from sp_work GROUP BY `date`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
SET flag=0;DROP TABLE IF EXISTS `temp_work`;
CREATE TEMPORARY TABLE temp_work (
today VARCHAR(10),
total INTEGER ,
status1 INT,
status2 INT,
status3 INT,
status4 INT,
status5 INT
) ENGINE=MEMORY ;
INSERT INTO temp_work(today,total) SELECT `date`,count(*) from sp_work where companyno = company_no GROUP BY `date` ORDER BY `date` desc LIMIT firstRow,listRow;OPEN cur1;
REPEAT
FETCH cur1 INTO aa;
UPDATE temp_work SET status1 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=1 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status2 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=2 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status3 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=3 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status4 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=4 and companyno = company_no) WHERE today=aa;
UPDATE temp_work SET status5 = (SELECT count(*) FROM sp_work WHERE `date`=aa and `status`=5 and companyno = company_no) WHERE today=aa;
UNTIL flagEND REPEAT;
CLOSE cur1;
SELECT * from temp_work;
END
比如,ORACLE不能直接执行 SELECT * from temp_work; 要通过游标来接收