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

最新推荐文章于 2025-08-01 19:03:39 发布

原创最新推荐文章于 2025-08-01 19:03:39 发布 · 3.4w 阅读

33 ·

CC 4.0 BY-SA版权

本文介绍了一种使用SQL进行数据查询的方法，通过示例展示了如何在指定的时间范围内对不同状态的数据进行汇总统计，并提供了两种实现方式以应对不同场景的需求。

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