如何在SQL中按TeamboardId分组查询每组首行数据

1. MySQL/MariaDB 实现 #

窗口函数写法（MySQL 8.0+） #

通过ROW_NUMBER()窗口函数按TeamboardId分组，以JobId升序排序后取每组第一条：

SELECT JobId, TeamboardId, HandLingTimeSeconds
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY TeamboardId ORDER BY JobId ASC) AS rn
    FROM Job
    WHERE HandLingTimeSeconds = '60'
) t
WHERE rn = 1;

兼容低版本写法（MySQL 5.x） #

用关联子查询筛选每组最小JobId对应的记录：

SELECT j1.*
FROM Job j1
WHERE HandLingTimeSeconds = '60'
AND JobId = (
    SELECT MIN(JobId)
    FROM Job j2
    WHERE j2.TeamboardId = j1.TeamboardId
    AND j2.HandLingTimeSeconds = '60'
);

2. SQL Server 实现 #

窗口函数写法 #

逻辑与MySQL一致：

SELECT JobId, TeamboardId, HandLingTimeSeconds
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY TeamboardId ORDER BY JobId ASC) AS rn
    FROM Job
    WHERE HandLingTimeSeconds = '60'
) t
WHERE rn = 1;

TOP 1 WITH TIES 简化写法 #

利用SQL Server特有语法直接返回分组首行：

SELECT TOP 1 WITH TIES *
FROM Job
WHERE HandLingTimeSeconds = '60'
ORDER BY ROW_NUMBER() OVER (PARTITION BY TeamboardId ORDER BY JobId ASC);

3. PostgreSQL 实现 #

窗口函数写法 #

SELECT JobId, TeamboardId, HandLingTimeSeconds
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY TeamboardId ORDER BY JobId ASC) AS rn
    FROM Job
    WHERE HandLingTimeSeconds = '60'
) t
WHERE rn = 1;

DISTINCT ON 特有写法 #

PostgreSQL专属语法，简洁实现分组取首行：

SELECT DISTINCT ON (TeamboardId) *
FROM Job
WHERE HandLingTimeSeconds = '60'
ORDER BY TeamboardId, JobId ASC;

说明 #

所有方案均先筛选HandLingTimeSeconds = '60'的记录，再按TeamboardId分组。如果需要按其他规则定义“第一行”，只需修改ORDER BY后的字段（比如创建时间、更新时间等）即可。

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