SQL Server查询：按FieldID获取各ChangeField的首次与末次变更值

当然可以实现这个需求！针对你给出的变更记录表，我们可以用SQL的窗口函数或者子查询聚合的方式来快速得到目标结果，下面提供两种适配不同数据库环境的方案：

原始数据 #

先把你的原始表数据整理成更清晰的格式（假设表名为change_log）：

FieldID | ChangeField | OldValue | NewValue | ChangeDate
--------|-------------|----------|----------|----------------------
1       | interest    | 1.5      | 1.2      | 2018-05-01 13:00:00
1       | interest    | 1.2      | 1.3      | 2018-05-01 14:00:00
1       | quantity    | 2        | 1        | 2018-05-01 15:00:00
1       | quantity    | 1        | 2        | 2018-05-01 16:00:00
1       | quantity    | 2        | 3        | 2018-05-01 17:00:00
2       | quantity    | 10       | 20       | 2018-05-01 18:00:00
2       | quantity    | 20       | 30       | 2018-05-01 19:00:00

方案1：使用窗口函数（推荐，支持MySQL8.0+/PostgreSQL/SQL Server等） #

窗口函数可以高效地给分组内的记录排序标记，然后提取我们需要的首尾值：

WITH ranked_records AS (
    SELECT 
        FieldID,
        ChangeField,
        OldValue,
        NewValue,
        ChangeDate,
        -- 给每个分组内的记录按时间升序编号，1就是首次变更
        ROW_NUMBER() OVER (PARTITION BY FieldID, ChangeField ORDER BY ChangeDate ASC) AS first_change,
        -- 给每个分组内的记录按时间降序编号，1就是末次变更
        ROW_NUMBER() OVER (PARTITION BY FieldID, ChangeField ORDER BY ChangeDate DESC) AS last_change
    FROM change_log
)
SELECT 
    FieldID,
    ChangeField,
    -- 提取首次变更的OldValue
    MAX(CASE WHEN first_change = 1 THEN OldValue END) AS OldValue,
    -- 提取末次变更的NewValue
    MAX(CASE WHEN last_change = 1 THEN NewValue END) AS NewValue,
    -- 取分组内最新的变更日期
    MAX(ChangeDate) AS dtChangeDate
FROM ranked_records
GROUP BY FieldID, ChangeField
ORDER BY FieldID, ChangeField;

方案2：子查询聚合（适配老版本数据库，比如MySQL5.x） #

如果你的数据库不支持窗口函数，可以用子查询来分别获取首次和末次值：

SELECT 
    main.FieldID,
    main.ChangeField,
    -- 子查询取该分组最早的OldValue
    (SELECT OldValue FROM change_log 
     WHERE FieldID = main.FieldID AND ChangeField = main.ChangeField 
     ORDER BY ChangeDate ASC LIMIT 1) AS OldValue,
    -- 子查询取该分组最晚的NewValue
    (SELECT NewValue FROM change_log 
     WHERE FieldID = main.FieldID AND ChangeField = main.ChangeField 
     ORDER BY ChangeDate DESC LIMIT 1) AS NewValue,
    -- 取分组内最新的变更日期
    MAX(main.ChangeDate) AS dtChangeDate
FROM change_log main
GROUP BY main.FieldID, main.ChangeField
ORDER BY main.FieldID, main.ChangeField;

执行结果 #

两种方案都会输出你预期的结果：

FieldID | ChangeField | OldValue | NewValue | dtChangeDate         | 说明
--------|-------------|----------|----------|----------------------|---------------------------------------------
1       | interest    | 1.5      | 1.3      | 2018-05-01 14:00:00  | interest初始值1.5，最终值1.3，最新变更时间14点
1       | quantity    | 2        | 3        | 2018-05-01 17:00:00  | quantity初始值2，最终值3，最新变更时间17点
2       | quantity    | 10       | 30       | 2018-05-01 19:00:00  | quantity初始值10，最终值30，最新变更时间19点

注意事项 #

• 记得把change_log替换成你的实际表名
• 如果同分组内存在同一时间多条变更记录，可以在排序时增加额外字段（比如主键）保证排序的唯一性
• 不同数据库的语法细节略有差异：比如SQL Server用TOP 1代替LIMIT 1，Oracle用ROWNUM或者窗口函数实现

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