SQL Server按scid分组获取每组首尾行fullName问题求助

我懂你碰到的麻烦了——用FIRST_VALUE和LAST_VALUE的时候总是出重复行，根本拿不到想要的每组首尾结果。这俩函数默认的窗口范围是从分组开头到当前行，所以每一行都会带上组内的首值，甚至LAST_VALUE还可能拿不到真正的最后一行（因为它默认只看到当前行之前的内容）。

下面给你几个靠谱的解决方案，都能得到你期望的输出：

方法一：用ROW_NUMBER()标记行号筛选首尾行 #

这个方法逻辑清晰，容易理解，也是我最常用的：

WITH ranked_data AS (
    SELECT 
        Id,
        scid,
        fullname,
        -- 给每个scid分组按Id排序，生成行号
        ROW_NUMBER() OVER (PARTITION BY scid ORDER BY Id) AS rn,
        -- 计算每个分组的总行数
        COUNT(*) OVER (PARTITION BY scid) AS total_rows
    FROM your_table_name
)
SELECT Id, scid, fullname
FROM ranked_data
-- 筛选行号为1（首行）或等于总行数（末行）的记录
WHERE rn = 1 OR rn = total_rows
ORDER BY scid, Id;

方法二：调整窗口范围+DISTINCT+UNION ALL（适配FIRST/LAST_VALUE） #

如果一定要用FIRST_VALUE和LAST_VALUE，得先调整窗口覆盖整个分组，再去重拆分：

WITH group_values AS (
    SELECT DISTINCT
        scid,
        FIRST_VALUE(Id) OVER (PARTITION BY scid ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_id,
        FIRST_VALUE(fullname) OVER (PARTITION BY scid ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_fullname,
        LAST_VALUE(Id) OVER (PARTITION BY scid ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_id,
        LAST_VALUE(fullname) OVER (PARTITION BY scid ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_fullname
    FROM your_table_name
)
-- 把首尾拆成两行
SELECT first_id AS Id, scid, first_fullname AS fullname
FROM group_values
UNION ALL
SELECT last_id AS Id, scid, last_fullname AS fullname
FROM group_values
ORDER BY scid, Id;

这里的关键是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，它让窗口覆盖整个分组，确保LAST_VALUE能拿到真正的最后一行数据。

方法三：GROUP BY找首尾Id再关联原表 #

这个方法更直观，适合数据量不大的场景：

SELECT t.Id, t.scid, t.fullname
FROM your_table_name t
JOIN (
    -- 先找到每个scid的最小Id（首行）和最大Id（末行）
    SELECT scid, MIN(Id) AS min_id, MAX(Id) AS max_id
    FROM your_table_name
    GROUP BY scid
) g ON t.Id = g.min_id OR t.Id = g.max_id
ORDER BY t.scid, t.Id;

这三种方法都能输出你想要的结果，推荐第一种或第三种，逻辑简单性能也稳定。

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