C#实习生 2022-03-31 07:57 采纳率: 60%
浏览 72
已结题

sql判断某一列,加载列的和

问题遇到的现象和发生背景
问题相关代码,请勿粘贴截图
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-01-31' then d.ryou end) LZY1, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-02-28' then d.ryou end) LZY2, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-03-31' then d.ryou end) LZY3, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-04-30' then d.ryou end) LZY4, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-05-31' then d.ryou end) LZY5, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-06-30' then d.ryou end) LZY6, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-07-31' then d.ryou end) LZY7, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-08-31' then d.ryou end) LZY8, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-09-30' then d.ryou end) LZY9, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-10-31' then d.ryou end) LZY10, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-11-30' then d.ryou end) LZY11, 
       sum(case when D.RQ BETWEEN '2022-01-01' and '2022-12-31' then d.ryou end) LZY12

运行结果及报错内容

img

我的解答思路和尝试过的方法

想用判断每月最后一天D.DYM的值是否为空来判断,应该是判断有一点问题

sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 1  then D.DYM end)) is null then null else '2022-01-31' end)then d.ryou end) LZY1,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 2  then D.DYM end)) is null then null else '2022-02-28' end)then d.ryou end) LZY2,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 3 then D.DYM end)) is null then null else '2022-03-31' end)then d.ryou end) LZY3,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 4 then D.DYM end)) is null then null else '2022-04-30' end)then d.ryou end) LZY4,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 5 then D.DYM end)) is null then null else '2022-05-31' end) then d.ryou end) LZY5,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 6 then D.DYM end)) is null then null else '2022-06-30' end) then d.ryou end) LZY6,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 7 then D.DYM end)) is null then null else '2022-07-31' end) then d.ryou end) LZY7,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 8 then D.DYM end)) is null then null else '2022-08-31' end) then d.ryou end) LZY8,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 9  then D.DYM end)) is null then null else '2022-09-30' end) then d.ryou end) LZY9,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 10  then D.DYM end)) is null then null else '2022-10-31' end) then d.ryou end) LZY10,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 11 then D.DYM end)) is null then null else '2022-11-30' end) then d.ryou end) LZY11,
sum(case when D.RQ BETWEEN '2022-01-01'  and (case when((case when year(D.RQ) =2022 and month(D.RQ) = 12 then D.DYM end)) is null then null else '2022-12-31' end) then d.ryou end) LZY12

我想要达到的结果

img

展开全部

  • 写回答

3条回答 默认 最新

  • bekote 2022-03-31 08:09
    关注

    case when要在分组求和之前做,像这样
    select month,sum(RQ) from (
    select month,(case when D.RQ BETWEEN '2022-01-01' and '2022-01-31' then d.ryou end) as RQ from test
    ) tmp group by month

    评论
    C#实习生 2022-03-31 08:17

    我想要的结果是本月每月数据,累增油不显示,代码一是正确的,但是是写死的

    回复
    bekote 回复 C#实习生 2022-03-31 09:00

    再套一层

    SELECT a,b,c,
        (CASE WHEN LZY1 IS NULL THEN NULL ELSE LZY1 END) LZY1,
        (CASE WHEN LZY2 IS NULL THEN NULL ELSE LZY1 + LZY2 END) LZY2,
        (CASE WHEN LZY3 IS NULL THEN NULL ELSE LZY1 + LZY2 + LZY3 END) LZY3,
    FROM
    (SELECT a,b,c,
           SUM(CASE WHEN D.RQ BETWEEN '2022-01-01' AND '2022-01-31' THEN d.ryou END) LZY1, 
           SUM(CASE WHEN D.RQ BETWEEN '2022-02-01' AND '2022-02-28' THEN d.ryou END) LZY2, 
           SUM(CASE WHEN D.RQ BETWEEN '2022-03-01' AND '2022-03-31' THEN d.ryou END) LZY3,
    FROM ...) tmp
    
    

    回复
  • DarkAthena ORACLE应用及数据库设计方案咨询师 2022-03-31 13:27
    关注

    说说看用的是什么数据库,不同数据库的写法不一样。
    这个思路其实可以这样,先简单做每个月的月汇总,竖着的,然后使用开窗函数的移动窗口,从第一行到当前行累加,最后再做个行列转换就行了,
    这样代码量比你目前的代码量少得多,而且可以达到你想要的效果

    评论
  • 仰望星空的代码 博客专家认证 2022-03-31 09:07
    关注
    评论
编辑
预览

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 4月1日
  • 赞助了问题酬金5元 3月31日
  • 修改了问题 3月31日
  • 修改了问题 3月31日
  • 展开全部
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部