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