SQL一对多关联查询去重并按关联表字段排序方案咨询

问题描述 #

表A与表B为一对多关系（一个A可对应多个B），想要查询A的全部数据并按B的字段排序，但执行以下SQL：

select * from A a left outer join B b on b.a_id = a.id order by b.id;

会根据A关联的B数量返回重复行（例如A关联3个B时会出现3条重复的A记录）。尝试用distinct结合order by时，会出现“not expression SELECTed”异常；添加fetch first 10 rows only仅在结果超过10条时生效。需要实现无重复的A列表按B的指定字段（如用户选择的B.description）排序，先在SQL中测试再用Java实现。

示例表结构 #

User表（对应A表） #

Contact表（对应B表） #

解决方案 #

方法1：聚合函数+分组排序 #

通过GROUP BY确保每个A记录唯一，同时用聚合函数（如MIN/MAX）提取B表的排序字段值，实现按B字段排序：

SELECT u.id, u.name, u.age
FROM User u
LEFT JOIN Contact c ON c.user_id = u.id
GROUP BY u.id, u.name, u.age
ORDER BY MIN(c.email); -- 可替换为MAX(c.email)，根据业务需求选择排序依据

该SQL会按User的唯一字段分组，返回无重复的User数据，同时以每个用户关联的最小email值进行排序，匹配示例期望结果。

方法2：窗口函数筛选唯一记录 #

用ROW_NUMBER()窗口函数给每个A关联的B记录编号，只保留每个A的第一条记录，再按指定字段排序：

SELECT id, name, age
FROM (
    SELECT u.id, u.name, u.age, c.email,
           ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY c.email) AS rn
    FROM User u
    LEFT JOIN Contact c ON c.user_id = u.id
) t
WHERE rn = 1
ORDER BY t.email;

这种方法灵活性更高，可指定B表字段的排序规则后，仅保留每个A的第一条关联记录，最终输出无重复的A列表并按目标字段排序。

方法3：子查询直接获取排序依据 #

主查询仅返回A表数据，ORDER BY中通过子查询获取每个A对应的B表字段值，写法简洁：

SELECT u.id, u.name, u.age
FROM User u
ORDER BY (SELECT MIN(c.email) FROM Contact c WHERE c.user_id = u.id);

该方式无需主查询关联B表，直接通过子查询提取排序所需的B字段值，确保返回的A记录完全无重复。

注意事项 #

• 若A无关联B记录，聚合函数/子查询会返回NULL，排序时NULL的位置可通过COALESCE函数指定默认值调整。
• 使用DISTINCT出现异常的原因是ORDER BY字段不在SELECT列表中，GROUP BY则可避免该问题，同时保证A记录唯一。

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