RICHEER COCA 2022-06-20 10:40 采纳率: 93.9%
浏览 441
已结题

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

--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


  • 写回答

4条回答 默认 最新

  • shinger126 2022-06-20 11:56
    关注

    ;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
    做一个积分分组表。先用会员卡和积分分组表做笛卡尔积关联,再左关联积分表汇总计算

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论 编辑记录
查看更多回答(3条)

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 6月23日
  • 已采纳回答 6月21日
  • 创建了问题 6月20日

悬赏问题

  • ¥15 我想通过python处理sybase IQ库的一些数据,但是不知道sybase IQ库怎么连接,之前连接都是用linux上的 dbisql
  • ¥15 安装llama-cpp-python==0.1.83失败
  • ¥20 管道轴向耦合水击问题
  • ¥60 补全networkx TODO部分。
  • ¥15 有内推吗,云计算linux运维方向
  • ¥30 sort cuteSV.vcf by bcftools用IGV可视化出现报错
  • ¥100 SOS!对STK中导出的天体图像进行质心提取有没有人做过啊
  • ¥15 python 欧式距离
  • ¥15 运行qteasy报错
  • ¥15 遗传算法解决有工序顺序约束的大规模FJSP问题