sql server 获取连续5年的统计数据

一、效果

sql 查询结果：

F_ReceiveAmt	YearTime
0.00	2018
0.00	2019
0.00	2020
66548.00	2021
5152.00	2022

二、SQL

-- PS: 利用sql server中的master..spt_values 
-- 连续5年时间 金额统计 无数据为0
-- 表:FM_Receive 金额字段:F_ReceiveAmt  时间字段:F_ReceiveDate
DECLARE @StartTime DATE = DATEADD(YEAR, -4, GETDATE()), -- 开始时间
        @EndTime DATE = GETDATE();                      -- 结束时间

SELECT ISNULL(a.value, 0) AS ReceiveAmt, b.YearTime
FROM
(
    SELECT ISNULL(SUM(F_ReceiveAmt), 0) AS value, name
    FROM
    (
        SELECT F_ReceiveAmt,
               CAST(YEAR(F_ReceiveDate) AS NVARCHAR(50)) AS name,
        FROM FM_Receive
        WHERE YEAR(F_ReceiveDate) > YEAR(GETDATE()) - 5
    ) y
    GROUP BY name
) a
    RIGHT JOIN
    (
        SELECT CONVERT(VARCHAR(4), DATEADD(YEAR, number, @StartTime), 120) AS YearTime
        FROM master..spt_values WITH (NOLOCK)
        WHERE type = 'P'
              AND number <= DATEDIFF(YEAR, @StartTime, @EndTime)
    ) b
        ON a.name = b.YearTime;