SELECT
aa.FOUND_DATE AS foundDate,
aa.entNum
FROM
(
SELECT
FOUND_DATE,
@total := @total + total1 AS entNum
FROM
(
SELECT YEAR
( FOUND_DATE ) AS FOUND_DATE,
sum( 1 ) AS total1
FROM
zszx.EM_ENTPRISE_MASTER
WHERE
ENABLED = 1
GROUP BY
YEAR ( FOUND_DATE )
) AS temp,
( SELECT @total := 0 ) AS T1
ORDER BY
FOUND_DATE
) aa
WHERE
aa.FOUND_DATE > YEAR (
now())-5
你看下我的理解是否正确
首先temp层取表里每年 ENABLED = 1 的记录数
aa层是按年度排序并累积记录数,也就是把逐年记录按顺序加合,得到截至该年的记录数
最后取最近5年的记录情况
如果上述理解符合需求的话,你试一下下面这个写法,看是否能正常执行
SELECT aa.FOUND_DATE
,aa.entNum
FROM (SELECT FOUND_DATE
,SUM(TOTAL1) OVER (ORDER BY FOUND_DATE) AS entNum
FROM (SELECT EXTRACT(YEAR FROM FOUND_DATE) AS FOUND_DATE
,SUM(1) AS TOTAL1
FROM zszx.EM_ENTPRISE_MASTER
WHERE ENABLED = 1
GROUP BY EXTRACT(YEAR FROM FOUND_DATE)
) temp
ORDER BY FOUND_DATE
) aa
WHERE aa.FOUND_DATE > EXTRACT(YEAR FROM SYSDATE) - 5