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

91zkwejq 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(223)

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

mysql Database mysql-workbench

来源：https://stackoverflow.com/questions/50325486/how-to-count-last-7-record-of-one-table-using-mysql-stored-procedure-in-my-quer

1条答案

按热度按时间

y53ybaqx1#

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

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\u localbodyid和i\u epid。

赞(0）回复(0）举报 2021-06-21

我来回答

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

1条答案

相关问题

热门标签

最新问答