如何在GROUP BY聚合查询中按Date分组获取MAX(SeqNumber)对应Value

嘿，我来帮你搞定这个SQL查询问题！

首先得说清楚你原来的语句为啥报错：当你用GROUP BY [Date]之后，SELECT子句里的列要么得是GROUP BY里的字段，要么得被聚合函数（比如MAX、SUM这类）包裹才行。你原来写的(CASE WHEN [SeqNumber]=MAX([SeqNumber]) THEN [Value] END)里，SeqNumber和Value都是行级别的字段，既没在GROUP BY里，也没被聚合，SQL Server根本不知道该取哪一行的这两个值，所以就抛出了8120错误。

下面给你两种可行的解决方案，你可以根据自己的SQL版本和习惯选：

方法一：用窗口函数（简洁高效，推荐） #

利用ROW_NUMBER()窗口函数给每个Date分组里的行按SeqNumber降序排名，直接取排名第一的那行（也就是SeqNumber最大的行），同时计算你需要的BestGold和BestSilver：

WITH RankedData AS (
    SELECT
        Date,
        Value,
        SeqNumber,
        -- 计算每个Date分组里GOLD的最大值
        MAX(CASE WHEN Code = 'GOLD' THEN Value END) OVER (PARTITION BY Date) AS BestGold,
        -- 计算每个Date分组里SILVER的最大值
        MAX(CASE WHEN Code = 'SILVER' THEN Value END) OVER (PARTITION BY Date) AS BestSilver,
        -- 给每个Date分组的行按SeqNumber降序排名，最大的排第1
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY SeqNumber DESC) AS RowRank
    FROM MyTable
)
SELECT
    Date,
    BestGold,
    BestSilver,
    Value AS HIGHEST  -- 这里就是对应最大SeqNumber的Value
FROM RankedData
WHERE RowRank = 1;

方法二：用子查询关联（兼容性更好） #

先通过子查询找出每个Date对应的最大SeqNumber，再关联原表拿到对应的Value，同时聚合计算BestGold和BestSilver：

SELECT
    t.Date,
    MAX(CASE WHEN t.Code = 'GOLD' THEN t.Value END) AS BestGold,
    MAX(CASE WHEN t.Code = 'SILVER' THEN t.Value END) AS BestSilver,
    t_max.Value AS HIGHEST
FROM MyTable t
-- 关联子查询，找到每个Date的最大SeqNumber对应的行
JOIN (
    SELECT Date, MAX(SeqNumber) AS MaxSeq
    FROM MyTable
    GROUP BY Date
) t_max ON t.Date = t_max.Date AND t.SeqNumber = t_max.MaxSeq
GROUP BY t.Date, t_max.Value;

这两种方法都能满足你的需求：按Date分组，拿到每个组里MAX(SeqNumber)对应的Value，同时算出BestGold和BestSilver的值。

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