CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_printnotprintdailyexpenses`(
in i_localBodyId varchar(10),
in i_epId int(20),
out printed INT(20),
out notprinted INT(20)
)
BEGIN
set printed=(select count(isPrinted) from tbl_dailyExpenses
where date((curdate() - 7)) and date(curdate())and
localBodyId =i_localBodyId and epId=i_epId and isPrinted=1 group by CURDATE()-7 );
set notprinted=(select count(isPrinted) from tbl_dailyExpenses
where date((curdate() - 7 )) and date(curdate())and
localBodyId =i_localBodyId and epId=i_epId and isPrinted=0 group by CURDATE()-7 );
END
我不认为你需要一个程序。一个简单的查询就足够了。根据我从这个问题(以及你问的另一个问题)中所了解到的,这应该会给你你想要的结果:
SELECT date,
localBodyId,
epId,
SUM(CASE WHEN isPrinted=1 THEN 1 ELSE 0 END) AS printed,
SUM(CASE WHEN isPrinted=0 THEN 1 ELSE 0 END) AS notprinted
FROM tbl_dailyExpenses
WHERE localBodyId = i_localBodyId AND epId = i_epId AND
date >= NOW() - INTERVAL 7 DAY
GROUP BY date, localBodyId, epId
ORDER BY date DESC
LIMIT 7
在此查询中,您需要将i_localBodyId和i_epId替换为要测试的值。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句