如何在MS SQL查询中获取max(o.Acct)及max(o.Acct)-1的两行数据

我来帮你搞定这个需求！你现在的查询只能拿到每组的最大Acct记录，要同时获取最大和次大（也就是max(o.Acct)和max(o.Acct)-1对应的行），有几种实用的方法，你可以根据数据情况选择：

方法1：用窗口函数ROW_NUMBER()（最通用） #

这个方法不管Acct是否连续都能用，思路是给每个分组（按ObjectID和Opertype分组）的记录按Acct降序排序号，然后取序号为1和2的行：

WITH RankedOperations AS (
    SELECT 
        o.Acct,
        o.ObjectID,
        o.Opertype,
        -- 按分组内Acct降序排号，最大的Acct是1，次大是2
        ROW_NUMBER() OVER (PARTITION BY o.ObjectID, o.Opertype ORDER BY o.Acct DESC) AS RowNum
    FROM Operations o
)
SELECT 
    Acct,
    ObjectID,
    Opertype
FROM RankedOperations
WHERE RowNum IN (1, 2)
-- 按分组和Acct排序，方便查看结果
ORDER BY ObjectID, Opertype, Acct DESC;

方法2：用DENSE_RANK()处理重复Acct #

如果你的分组里可能存在多个相同的最大Acct（比如同一组里有两行Acct都是100），用DENSE_RANK()会更合适——它会给相同Acct的记录分配相同的排名，这样能保留所有最大Acct的行，再加上次大的：

WITH RankedOperations AS (
    SELECT 
        o.Acct,
        o.ObjectID,
        o.Opertype,
        DENSE_RANK() OVER (PARTITION BY o.ObjectID, o.Opertype ORDER BY o.Acct DESC) AS RankNum
    FROM Operations o
)
SELECT 
    Acct,
    ObjectID,
    Opertype
FROM RankedOperations
WHERE RankNum IN (1, 2)
ORDER BY ObjectID, Opertype, Acct DESC;

方法3：关联子查询（适合Acct连续的场景） #

如果你的Acct是严格连续递增的（比如每组里的Acct不会跳号），可以用关联子查询先找到每组的最大Acct，再筛选出等于最大或最大减1的行：

SELECT 
    o.Acct,
    o.ObjectID,
    o.Opertype
FROM Operations o
INNER JOIN (
    -- 先拿到每组的最大Acct
    SELECT 
        ObjectID,
        Opertype,
        MAX(Acct) AS MaxAcct
    FROM Operations
    GROUP BY ObjectID, Opertype
) AS GroupMax 
    ON o.ObjectID = GroupMax.ObjectID 
    AND o.Opertype = GroupMax.Opertype
-- 筛选出最大和次大的Acct
WHERE o.Acct >= GroupMax.MaxAcct - 1
ORDER BY o.ObjectID, o.Opertype, o.Acct DESC;

小提示 #

• 如果你的数据量很大，窗口函数的性能通常会比关联子查询更好，因为它只需要扫描一次表。
• 记得测试不同方法在你的实际数据上的效果，尤其是当有重复Acct或者Acct不连续的时候。

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