mysql查询，返回多个统计结果

最新推荐文章于 2024-08-02 23:34:49 发布

那些年欠下的技术债

最新推荐文章于 2024-08-02 23:34:49 发布

阅读量848

点赞数

本文链接：https://blog.csdn.net/pdsuxueyuan/article/details/100152901

版权

1.SUM(IF）

SELECT 
workinfo.workType AS workType,
workinfo.workName AS workName,
workinfo.warehouse AS warehouse,
COUNT(*) AS totalCount,
SUM(IF(workinfo.workStatus='04',1,0)) AS completedCount,
SUM(IF(workinfo.workStatus='01',1,0)) AS initCount,
SUM(IF(workinfo.workStatus='02',1,0)) AS distributeCount
FROM
td_wcs_work workinfo
WHERE
workinfo.createTime>='2019-08-12'
AND 
workinfo.updateTime<='2019-08-13'
GROUP BY
workinfo.warehouse,workinfo.workType

SUM中的1,0代表如果满足条件累计加1，否则加0
2.count()

SELECT 
COUNT(*) AS totalCount,
COUNT(workinfo.workStatus='04' OR NULL) AS completedCount,
COUNT(workinfo.workStatus='01' OR NULL) AS initCount,
COUNT(workinfo.workStatus='02' OR NULL) AS distributeCount
FROM
td_wcs_work workinfo
WHERE
workinfo.createTime>='2019-08-12'
AND 
workinfo.updateTime<='2019-08-13'
GROUP BY
workinfo.warehouse,workinfo.workType

count()中的null代表满足条件统计，否则统计null