mysql - sql语句修改

Question

SELECT
    A.SecuCode,
    B.NetProfit,
    B.EndDate,
    TradingDay = B.InfoPublDate
FROM
    SecuMain A,
    LC_IncomeStatementAll B
WHERE
    A.CompanyCode = B.CompanyCode
AND B.EndDate >= '2010-01-25'
AND B.EndDate <= '2016-02-05'
AND CONVERT (VARCHAR, B.EndDate, 120) LIKE '%-12-31%'
ORDER BY
    SecuCode,
    EndDate DESC

对于以上结果，我想按照对每个SecuCode去最近五年的NetProfit求平均（表中有的SecuCode对应五年的数据，有的是六年的），请问sql语句怎么修改好~

Answer 1

SELECT
    A.SecuCode,
    AVG(B.NetProfit)
FROM
    SecuMain A,
    LC_IncomeStatementAll B
WHERE
    A.CompanyCode = B.CompanyCode
AND B.EndDate >= '2010-01-25'
AND B.EndDate <= '2016-02-05'
AND CONVERT (VARCHAR, B.EndDate, 120) LIKE '%-12-31%'
GROUP BY
    A.SecuCode

Answer 2

我觉得用Group By 能实现
先按 A.SecuCode分组
再对每个组内的 B.NetProfit求平均值

Answer 3

用row_number()over () 能搞定

Answer 4

下面是按时间顺序取五笔数据，然后取平均值

SELECT SecuCode,AVG(NetProfit) FROM (
SELECT
    A.SecuCode,
    B.NetProfit,
    B.EndDate,
    TradingDay = B.InfoPublDate,
    ROW_NUMBER()OVER(PARTITION BY A.SecuCode ORDER BY B.EndDat DESC) AS rn
FROM
    SecuMain A,
    LC_IncomeStatementAll B
WHERE
    A.CompanyCode = B.CompanyCode
AND B.EndDate >= '2010-01-25'
AND B.EndDate <= '2016-02-05'
AND CONVERT (VARCHAR, B.EndDate, 120) LIKE '%-12-31%'
--ORDER BY
--    SecuCode,
--    EndDate DESC
) t WHERE t.rn<=5
GROUP BY SecuCode