Sql中 Count 统计的记录 为 空时 如何显示 0 ?请专家答疑解惑

Question

--Sql中 Count 统计的记录 为 空时 如何显示 0 ?

SNO为会员ID,积分val的记录如下,需要完成对val积分123456789及包含10以上的积分的统计

,例如:积分5记录 为 空时 如何显示 0 ?,执行下面的脚本,积分5记录 为 空时 显示应该是0,实际上结果里没有.请专家答疑解惑,谢谢

;WITH T AS(
SELECT '2022160'SNO,'2'val
UNION ALL SELECT '2022160','7'
UNION ALL SELECT '2022160','8'
UNION ALL SELECT '2022160','8'
UNION ALL SELECT '2022160','2'
UNION ALL SELECT '2022160','8'
UNION ALL SELECT '2022160','4'
UNION ALL SELECT '2022160','9'
UNION ALL SELECT '2022160','9'
UNION ALL SELECT '2022160','6'
UNION ALL SELECT '2022160','3'
UNION ALL SELECT '2022160','1'
UNION ALL SELECT '2022160','3'
UNION ALL SELECT '2022160','6'
UNION ALL SELECT '2022160','4'
UNION ALL SELECT '2022160','10'
UNION ALL SELECT '2022160','2'
UNION ALL SELECT '2022160','3'
UNION ALL SELECT '2022160','4'
UNION ALL SELECT '2022160','2'
)

SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=1 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=2 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=3 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=4 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=5 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=6 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=7 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=8 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val=9 GROUP BY SNO,VAL
UNION ALL
SELECT SNO,COUNT(VAL) AS WM_RpNr from T where val>=10 GROUP BY SNO,VAL

想要得到的结果
----------------------------------------------------------------------
SNO    WM_RpNr
2022160    1
2022160    4
2022160    3
2022160    3
2022160    0
2022160    2
2022160    1
2022160    3
2022160    2
2022160    1

Answer 1

;WITH T AS(
SELECT '2022160'SNO,'2'val
UNION ALL SELECT '2022160','7'
UNION ALL SELECT '2022160','8'
UNION ALL SELECT '2022160','8'
UNION ALL SELECT '2022160','2'
UNION ALL SELECT '2022160','8'
UNION ALL SELECT '2022160','4'
UNION ALL SELECT '2022160','9'
UNION ALL SELECT '2022160','9'
UNION ALL SELECT '2022160','6'
UNION ALL SELECT '2022160','3'
UNION ALL SELECT '2022160','1'
UNION ALL SELECT '2022160','3'
UNION ALL SELECT '2022160','6'
UNION ALL SELECT '2022160','4'
UNION ALL SELECT '2022160','10'
UNION ALL SELECT '2022160','2'
UNION ALL SELECT '2022160','3'
UNION ALL SELECT '2022160','4'
UNION ALL SELECT '2022160','2'
),
s as (select distinct SNO from t),
v as (select 1 as valgroup,1 as minval,1 as maxval
union all
select 2 as valgroup,2 as minval,2 as maxval
union all
select 3 as valgroup,3 as minval,3 as maxval
union all
select 4 as valgroup,4 as minval,4 as maxval
union all
select 5 as valgroup,5 as minval,5 as maxval
union all
select 6 as valgroup,6 as minval,6 as maxval
union all
select 7 as valgroup,7 as minval,9 as maxval
union all
select 8 as valgroup,8 as minval,8 as maxval
union all
select 9 as valgroup,9 as minval,9 as maxval
union all
select 10 as valgroup,10 as minval,999999 as maxval)
select s.SNO,count(t.val) cnt,v.valgroup
from v join s on 1=1
left join t on t.val between v.minval and v.maxval and s.SNO=t.SNO
group by s.SNO,v.valgroup
做一个积分分组表。先用会员卡和积分分组表做笛卡尔积关联，再左关联积分表汇总计算