sql 分组统计，数据为空时显示0

最新推荐文章于 2024-08-05 10:05:12 发布

淡淡飘零

最新推荐文章于 2024-08-05 10:05:12 发布

阅读量3.3w

点赞数 20

本文链接：https://blog.csdn.net/qq_29954875/article/details/80203119

版权

1.未添加时间搜索条件，查询结果如下

2. 添加时间添加后，未有符合条件的数据，呈现的结果如下：

实际想要的结果应该是如下情况：

直接上代码：

SELECT
name,
SUM (Total) AS total,
SUM (Untreated) AS untreated,
SUM (Dealing) AS dealing,
SUM (Handled) AS handled
FROM
(
SELECT oe.Name AS name, COUNT (aj.SjZt) AS Total,
SUM ( CASE aj.SjZt WHEN '0' THEN 1 ELSE 0 END ) AS Untreated,
SUM ( CASE aj.SjZt WHEN '1' THEN 1 ELSE 0 END ) AS Dealing,
SUM ( CASE aj.SjZt WHEN '2' THEN 1 ELSE 0 END ) AS Handled
FROM Organize oe
LEFT JOIN AJ_Ajxx aj ON aj.UserDept = oe.id
WHERE (oe.wid = '1' OR oe.PID = '1')
AND aj.AddTime BETWEEN '2019/5/1 0:00:00'
AND '2019/5/31 23:59:59'
GROUP BY
oe.Name
UNION
SELECT '11' AS name, '0' AS Total, '0' AS Untreated, '0' AS Dealing, '0' AS Handled
UNION
SELECT '22' AS name, '0' AS Total, '0' AS Untreated, '0' AS Dealing, '0' AS Handled
UNION
SELECT '33' AS name, '0' AS Total, '0' AS Untreated, '0' AS Dealing, '0' AS Handled
) c
GROUP BY

c.name

注意：

红框部分如果不是固定，那可以先将展示的的先查询出来，然后将循环拼接上语句:

写法二：

if exists (SELECT
aj.UserDept,
oe.Name,
COUNT (aj.SjZt) AS Total,
SUM ( CASE aj.SjZt WHEN '0' THEN 1 ELSE 0 END ) AS Untreated,
SUM ( CASE aj.SjZt WHEN '1' THEN 1 ELSE 0 END ) AS Dealing,
SUM ( CASE aj.SjZt WHEN '2' THEN 1 ELSE 0 END ) AS Handled
FROM
Organize oe
LEFT JOIN AJ_Ajxx aj ON aj.UserDept = oe.id
WHERE
(oe.wid = '1' OR oe.PID = '1')
and (aj.AddTime >'2018/5/1 00:00:00' and aj.AddTime <'2018/5/31 23:59:59' and aj.AddTime between '2018/5/1 00:00:00' and '2018/5/31 23:59:59')
GROUP BY
aj.UserDept,
oe.Name
)
begin
SELECT
aj.UserDept,
oe.Name,
COUNT (aj.SjZt) AS Total,
SUM ( CASE aj.SjZt WHEN '0' THEN 1 ELSE 0 END ) AS Untreated,
SUM ( CASE aj.SjZt WHEN '1' THEN 1 ELSE 0 END ) AS Dealing,
SUM ( CASE aj.SjZt WHEN '2' THEN 1 ELSE 0 END ) AS Handled
FROM
Organize oe
LEFT JOIN AJ_Ajxx aj ON aj.UserDept = oe.id
WHERE
(oe.wid = '1' OR oe.PID = '1')
and (aj.AddTime >'2018/5/1 00:00:00' and aj.AddTime <'2018/5/31 23:59:59' and aj.AddTime between '2018/5/1 00:00:00' and '2018/5/31 23:59:59')
GROUP BY
aj.UserDept,
oe.Name
end
else
begin
SELECT
aj.UserDept,
oe.Name,
Total=0,Untreated=0,Dealing=0,Handled=0
FROM
Organize oe
LEFT JOIN AJ_Ajxx aj ON aj.UserDept = oe.id
WHERE
(oe.wid = '1' OR oe.PID = '1')

GROUP BY
aj.UserDept,
oe.Name

end