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

91zkwejq  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(222)
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_printnotprintdailyexpenses`(
  2. in i_localBodyId varchar(10),
  3. in i_epId int(20),
  4. out printed INT(20),
  5. out notprinted INT(20)
  6. )
  7. BEGIN
  8. set printed=(select count(isPrinted) from tbl_dailyExpenses
  9. where date((curdate() - 7)) and date(curdate())and
  10. localBodyId =i_localBodyId and epId=i_epId and isPrinted=1 group by CURDATE()-7 );
  11. set notprinted=(select count(isPrinted) from tbl_dailyExpenses
  12. where date((curdate() - 7 )) and date(curdate())and
  13. localBodyId =i_localBodyId and epId=i_epId and isPrinted=0 group by CURDATE()-7 );
  14. END
y53ybaqx

y53ybaqx1#

我认为你不需要做手术。一个简单的查询就足够了。根据我对这个问题(以及你问的另一个问题)的理解,这应该会给你想要的结果:

  1. SELECT date,
  2. localBodyId,
  3. epId,
  4. SUM(CASE WHEN isPrinted=1 THEN 1 ELSE 0 END) AS printed,
  5. SUM(CASE WHEN isPrinted=0 THEN 1 ELSE 0 END) AS notprinted
  6. FROM tbl_dailyExpenses
  7. WHERE localBodyId = i_localBodyId AND epId = i_epId AND
  8. date >= NOW() - INTERVAL 7 DAY
  9. GROUP BY date, localBodyId, epId
  10. ORDER BY date DESC
  11. LIMIT 7

在这个查询中,需要用要测试的值替换i\u localbodyid和i\u epid。

相关问题