SQL Server实现按基金取近3期日期关联成本的PIVOT行转列查询

基础信息 #

现有表MyTable存储基金成本数据，样例数据如下：

Fund  |  EffectiveDate | SomeOtherColumns | COST
F123  |  2022-04-25    |  something       | 345
F123  |  2022-04-24    |   fdsdfdff       | 340
F123  |  2022-04-20    |   hi             | 360
F123  |  2022-04-17    |   hello          | 810
F456  |  2022-04-28    |  some other fund | 110
F456  |  2022-04-26    |  some other fund | 220
F456  |  2022-04-25    |  some other fund | 460
F456  |  2022-04-15    |  some other fund | 215

表结构：

CREATE TABLE [dbo].[MyTable](
    [Fund] [NCHAR](10) NOT NULL,
    [EffectiveDate] [DATE] NOT NULL,
    [SomeOtherColumns] [NVARCHAR](50) NULL,
    [Cost] [INT] NOT NULL
) ON [PRIMARY]
GO

需求为每个基金返回1条记录，展示最新日期的基础信息，同时关联最新、前1个有效日期、前2个有效日期对应的成本，期望输出：

Fund  |  EffectiveDate | SomeOtherColumns | COST | COST DAY BEFORE | COST DAY BEFORE THAT |
F123  |  2022-04-25    | something        | 345  | 340             | 360                  |
F456  |  2022-04-28    | some other fund  | 110  | 220             | 460                  |

原有写法的核心问题：

• 未按基金分组取TOP3，直接TOP(3)只会返回全表最新的3条数据
• PIVOT的IN子句必须明确指定转列后的枚举值，不能直接传入原表字段
• 未对不同日期的成本做排序标记，数据库无法识别列与日期的对应关系

正确SQL代码 #

基于PIVOT语法实现，先用窗口函数给每个基金下的日期倒序编号，同时标记每个基金的最新日期及对应字段，再做行转列：

WITH AllRanked AS (
    SELECT 
        Fund,
        Cost,
        -- 按基金分组，日期倒序编号：1=最新日期，2=前1个日期，3=前2个日期
        ROW_NUMBER() OVER (PARTITION BY Fund ORDER BY EffectiveDate DESC) AS DateRank,
        -- 取每个基金最新的生效日期
        FIRST_VALUE(EffectiveDate) OVER (PARTITION BY Fund ORDER BY EffectiveDate DESC) AS LatestEffectiveDate,
        -- 取每个基金最新日期对应的其他列值
        FIRST_VALUE(SomeOtherColumns) OVER (PARTITION BY Fund ORDER BY EffectiveDate DESC) AS LatestOtherColumns
    FROM MyTable
),
-- 只保留每个基金最新的3条数据，减少计算量
Top3Data AS (
    SELECT * FROM AllRanked WHERE DateRank <= 3
)
SELECT 
    Fund,
    LatestEffectiveDate AS EffectiveDate,
    LatestOtherColumns AS SomeOtherColumns,
    [1] AS COST,
    [2] AS [COST DAY BEFORE],
    [3] AS [COST DAY BEFORE THAT]
FROM Top3Data
PIVOT (
    SUM(Cost)
    FOR DateRank IN ([1], [2], [3])
) AS PivotedResult
-- 按基金分组去重，每个基金仅返回1条结果
GROUP BY Fund, LatestEffectiveDate, LatestOtherColumns

说明 #

• 如果同一个基金同一个EffectiveDate存在多条重复数据，可将SUM(Cost)替换为MAX(Cost)，或提前在CTE中做去重处理
• 执行后返回结果与预期完全一致

内容的提问来源于stack exchange，提问作者UnskilledCoder