SqlServer使用GroupBy分组时, 设置当查询不到某个分组时, 默认赋值一个0

现在有个需求, 需要将分组查询并统计个数后返回数据,但是当数据中没有对应的分组时就查询不到对应的数据, 比如使用下面的Sql语句查询(State为Table1的字段,值为int类型)

点击查看代码

select 
	 case State
		when 0 then '开工'
		when 1 then '等待'
		when 2 then '完工'
		when -2 then '待审核'
		when -1 then '驳回'
		end as 状态,
		count(Autoid) as 订单数量 
	from Table1  group by State
go

使用上面的语句查询得到的结果如图, 可以看出统计出来的只有开工、等待和完工, 没有待审核和驳回的数据,因为表内没有这两个状态的数据, 现在需要当没有数据时显示订单数量为0

优化一下Sql语句

点击查看代码

WITH StateTable AS (
	SELECT -2 AS State UNION ALL
	SELECT -1 UNION ALL
	SELECT 0 UNION ALL
	SELECT 1 UNION ALL
	SELECT 2
)
SELECT 
    case st.State
		when 0 then '开工'
		when 1 then '等待'
		when 2 then '完工'
		when -2 then '待审核'
		when -1 then '驳回'
		end as 订单状态,
    COALESCE(COUNT(ji.id), 0) AS 订单数量
FROM StateTable st
LEFT JOIN JOBINFO ji ON st.State = ji.State and CONVERT(varchar, ji.PlanDateTime, 120) LIKE '%2023-%'
GROUP BY st.State;

以上的sql语句((这里加多了一个条件,查询PlanDateTime是2023年的数据),解释一下:这个SQL语句首先使用WITH子句创建了一个临时表StateTable，包含了所有可能的State值（-2、-1、0、1、2）。然后，将StateTable与JOBINFO表进行左连接。接着使用CASE语句将State值转换为对应的中文描述。最后，使用COALESCE函数将NULL值替换为0，并按照State分组统计id的数量。下面就是运行的结果