mysql - 如何使用 mysql 存储过程计算一个表的最后 7 条记录，在我的查询中，我希望 count isprinted 当 isprinted=1 时打印

Question

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

Answer 1

我认为您不需要任何程序。一个简单的查询就足够了。根据我从这个问题(以及你问的另一个问题)中了解到的内容，这应该会给你你想要的结果:

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 替换为您要测试的值。