--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
;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
做一个积分分组表。先用会员卡和积分分组表做笛卡尔积关联，再左关联积分表汇总计算
count永不为空，它有且只有一行返回记录
关键是后面不要写where
写了where你可以连这一条都给过滤掉了
你应该先group by sno,val，去掉那一堆union all
然后外面再套一层select，把大于10的合并一下
这本来就是一个聚合查询搞定的事，怎么让你写这么复杂
得用子查询，结合IFNULL函数。先用子查询把有积分的先统计并计算count，把这个查询当作临时表返回给上层，上层同样是还是积分表，用积分表和这个临时表做左外连结，使用 IFNULL 判断cnt是否为空就行了。可以看看这个题：https://leetcode.cn/problems/market-analysis-i/
希望给个采纳。
这是因为你where+group导致的。
因为where过滤数据，留下了0条，然后你分组，这个时候你的数据已经是空了，分组自然不存在，没有分组也就没有了行。