BEGIN
DECLAREaaVARCHAR(50);
DECLAREflagINT;
declarecur1CURSORforselect`date`fromsp_workGROUPBY`date`;
DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=1;
SETflag=0;
DROPTABLEIFEXISTS`temp_work`;
CREATETEMPORARYTABLEtemp_work(
todayVARCHAR(10),
totalINTEGER,
status1INT,
status2INT,
status3INT,
status4INT,
status5INT
)ENGINE=MEMORY;
INSERTINTOtemp_work(today,total)SELECT`date`,count(*)fromsp_workwherecompanyno=company_noGROUPBY`date`ORDERBY`date`descLIMITfirstRow,listRow;
OPENcur1;
REPEAT
FETCHcur1INTOaa;
UPDATEtemp_workSETstatus1=(SELECTcount(*)FROMsp_workWHERE`date`=aaand`status`=1andcompanyno=company_no)WHEREtoday=aa;
UPDATEtemp_workSETstatus2=(SELECTcount(*)FROMsp_workWHERE`date`=aaand`status`=2andcompanyno=company_no)WHEREtoday=aa;
UPDATEtemp_workSETstatus3=(SELECTcount(*)FROMsp_workWHERE`date`=aaand`status`=3andcompanyno=company_no)WHEREtoday=aa;
UPDATEtemp_workSETstatus4=(SELECTcount(*)FROMsp_workWHERE`date`=aaand`status`=4andcompanyno=company_no)WHEREtoday=aa;
UPDATEtemp_workSETstatus5=(SELECTcount(*)FROMsp_workWHERE`date`=aaand`status`=5andcompanyno=company_no)WHEREtoday=aa;
UNTILflag
ENDREPEAT;
CLOSEcur1;
SELECT*fromtemp_work;
END
分 -->
|
