SQL如何按priority列最高值分组 获取各部门最高优先级记录

现有department、category两张数据表，常规关联查询时同一部门会重复返回多条记录。需要根据category表的priority字段做部门维度分组：priority取值为1、2、3，数值1代表最高优先级，最终每个部门仅返回对应最高优先级的单条记录。

示例原始数据：

id    dept_name     dept_category  priority
1      Cardio         category 2     2
2      Ortho          category 3     3
3      Ortho          category 1     1
4      ENT            category 1     1
5      Ortho          category 2     2

期望返回结果：

id    dept_name     dept_category  priority
1      Cardio         category 2     2
3      Ortho          category 1     1
4      ENT            category 1     1

通用写法（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

用窗口函数实现，逻辑简洁、性能更优：

SELECT id, dept_name, dept_category, priority
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY dept_name
            ORDER BY priority ASC, id ASC
        ) AS row_rank
    FROM -- 此处替换为你实际的两表关联逻辑
        department d
        INNER JOIN category c ON d.category_id = c.id
) tmp
WHERE row_rank = 1;

逻辑说明：

• 按dept_name（部门）做分区，同部门内按priority升序排序（数值越小优先级越高，最高优先级记录会排在分区第一位），追加id ASC排序是为了同部门存在多条相同最高优先级记录时，固定取id最小的那条，避免结果随机
• 外层筛选行号为1的记录，即可得到每个部门对应最高优先级的单条结果，和期望输出完全匹配。

兼容旧版数据库写法（支持MySQL5.x等不支持窗口函数的环境） #

通过子查询先找到每个部门的最高优先级值，再关联回原表取对应记录：

SELECT t1.*
FROM (
    -- 此处替换为你实际的两表关联逻辑
    SELECT d.id, d.dept_name, c.dept_category, c.priority
    FROM department d
    INNER JOIN category c ON d.category_id = c.id
) t1
INNER JOIN (
    SELECT 
        dept_name,
        MIN(priority) AS top_priority
    FROM (
        SELECT d.dept_name, c.priority
        FROM department d
        INNER JOIN category c ON d.category_id = c.id
    ) t
    GROUP BY dept_name
) t2
ON t1.dept_name = t2.dept_name
AND t1.priority = t2.top_priority
GROUP BY t1.dept_name;

注意：如果同部门存在多条priority同为最高级的记录，该写法默认返回同优先级下的第一条记录，如果需要保留所有同最高优先级的记录，去掉末尾的GROUP BY t1.dept_name即可。

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