select aa.months,sum(num) over(order by aa.months) from ( select trunc(regdate,'mm') months,count(*) num from a where regDate>=trunc(sysdate,'yyyy') group by trunc(regdate,'mm') ) aa
没看仔细,还要根据LEVELSELECT aa.LEVEL, aa.months, SUM (num) OVER (PARTITION BY aa.LEVEL ORDER BY aa.months) FROM (SELECT a.LEVEL, TRUNC (regdate, 'mm') months, COUNT (*) num FROM a WHERE regdate >= TRUNC (SYSDATE, 'yyyy') GROUP BY a.LEVEL, TRUNC (regdate, 'mm')) aa
select m.level , to_char(m.regDate,'yyyy-mm') yue, sum((select count(*) from tb n where n.level = m.level and to_char(n.regDate,'yyyy-mm') >= to_char(m.regDate,'yyyy-mm'))) 会员总量 from tb m group by m.level , to_char(m.regDate,'yyyy-mm')
就是所谓的窗口聚合函数加上行列转置的效果 SELECT LEVEL AS "级别", MAX(CASE WHEN to_char(RegOfMonth,'MM')='01' THEN accumulative_total END) AS "一月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='02' THEN accumulative_total END) AS "二月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='03' THEN accumulative_total END) AS "三月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='04' THEN accumulative_total END) AS "四月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='05' THEN accumulative_total END) AS "五月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='06' THEN accumulative_total END) AS "六月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='07' THEN accumulative_total END) AS "七月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='08' THEN accumulative_total END) AS "八月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='09' THEN accumulative_total END) AS "九月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='10' THEN accumulative_total END) AS "十月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='11' THEN accumulative_total END) AS "十一月", MAX(CASE WHEN to_char(RegOfMonth,'MM')='12' THEN accumulative_total END) AS "十二月" FROM ( SELECT LEVEL, SUM(total) over(PARTITION BY LEVEL ORDER BY RegOfMonth ROWS UNBOUNDED PRECEDING) AS accumulative_total ( SELECT LEVEL, TRUNC(regDate,'MM') AS RegOfMonth, COUNT(recordID) AS total FROM yourtable WHERE to_char(regDate,'YYYY')='2008' GROUP BY LEVEL,trunc(regDate,'MM') ) ) GROUP BY LEVEL
from (
select trunc(regdate,'mm') months,count(*) num
from a
where regDate>=trunc(sysdate,'yyyy')
group by trunc(regdate,'mm')
) aa
SUM (num) OVER (PARTITION BY aa.LEVEL ORDER BY aa.months)
FROM (SELECT a.LEVEL, TRUNC (regdate, 'mm') months, COUNT (*) num
FROM a
WHERE regdate >= TRUNC (SYSDATE, 'yyyy')
GROUP BY a.LEVEL, TRUNC (regdate, 'mm')) aa
select m.level , to_char(m.regDate,'yyyy-mm') yue, sum((select count(*) from tb n where n.level = m.level and to_char(n.regDate,'yyyy-mm') >= to_char(m.regDate,'yyyy-mm'))) 会员总量 from tb m group by m.level , to_char(m.regDate,'yyyy-mm')
SELECT LEVEL AS "级别",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='01' THEN accumulative_total END) AS "一月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='02' THEN accumulative_total END) AS "二月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='03' THEN accumulative_total END) AS "三月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='04' THEN accumulative_total END) AS "四月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='05' THEN accumulative_total END) AS "五月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='06' THEN accumulative_total END) AS "六月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='07' THEN accumulative_total END) AS "七月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='08' THEN accumulative_total END) AS "八月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='09' THEN accumulative_total END) AS "九月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='10' THEN accumulative_total END) AS "十月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='11' THEN accumulative_total END) AS "十一月",
MAX(CASE WHEN to_char(RegOfMonth,'MM')='12' THEN accumulative_total END) AS "十二月"
FROM
(
SELECT LEVEL,
SUM(total) over(PARTITION BY LEVEL ORDER BY RegOfMonth ROWS UNBOUNDED PRECEDING) AS accumulative_total
(
SELECT
LEVEL,
TRUNC(regDate,'MM') AS RegOfMonth,
COUNT(recordID) AS total
FROM yourtable
WHERE to_char(regDate,'YYYY')='2008'
GROUP BY LEVEL,trunc(regDate,'MM')
)
)
GROUP BY LEVEL