SQL每日一题(20201107)希望将每个月之前的月份进行累加

SQL每日一题(20211223)

/*
才开始想用sum()over()发现 total不是顺序排好了的 所以行不通
1.这里的思路是row_number 来进行排序 跟 另外一个row_number 进行排序的进行内连接 条件是 a.pm<=b.pm
这样b.month得到的为 7(包含了一月份到7月份的值)+6+5+4+3+2+1=28个
2.根据month进行 a.total求和
这种自连接的方法有点不太好理解
*/

select b.month, sum(a.total) as cussum
from (SELECT *, row_number() over () as pm FROM T1109) a,
     (SELECT *, row_number() over () as pm FROM T1109) b where a.pm<=b.pm
GROUP by b.month

SELECT `month`,
       sum(total) over (
           ORDER BY CASE
                        WHEN `month` = '一月份' THEN 1
                        WHEN `month` = '二月份' THEN 2
                        WHEN `month` = '三月份' THEN 3
                        WHEN `month` = '四月份' THEN 4
                        WHEN `month` = '五月份' THEN 5
                        WHEN `month` = '六月份' THEN 6
                        WHEN `month` = '七月份' THEN 7
                        WHEN `month` = '八月份' THEN 8
                        WHEN `month` = '九月份' THEN 9
                        WHEN `month` = '十月份' THEN 10
                        WHEN `month` = '十一月份' THEN 11
                        WHEN `month` = '十二月份' THEN 12 END ) AS total FROM t1109

SELECT *
 ,SUM(TOTAL) OVER(
ORDER BY locate(SUBSTRING(MONTH,1,1)
,'一,二,三,四,五,六,七,八,九,十,十一,十二') ) 
AS SUMTOTAL
FROM T1109