如何通过JOIN实现按测试分数筛选含C组的前5个专业并降序排序？

现有两张业务表：

majors表 #

tests表 #

需求说明 #

• 生成按测试分数降序排列的前5个专业列表
• 分组关联规则：C组是A组与B组的组合，需关联这两个组的所有专业
• 结果需包含test_id、group、test_score、major_name、major_score字段
• 最终结果限制为5条

用户尝试的基础JOIN语句：

SELECT m.id, m.name, m.group, m.score
FROM majors AS m
JOIN tests AS t ON m.group = t.group
ORDER BY t.score DESC, m.score DESC
LIMIT 5;

该语句直接限制总结果为5条，无法满足C组关联A、B组专业的需求，需调整实现逻辑。

可以通过条件JOIN实现分组的特殊关联逻辑，同时结合排序和限制得到目标结果。具体SQL如下：

SELECT 
  t.id AS test_id,
  t.group,
  t.score AS test_score,
  m.name AS major_name,
  m.score AS major_score
FROM tests t
LEFT JOIN majors m 
  ON t.group = m.group 
  OR (t.group = 'C' AND m.group IN ('A', 'B'))
ORDER BY t.score DESC, m.score DESC
LIMIT 5;

逻辑说明 #

1. JOIN条件处理：
   • 普通分组（A/B）直接按group字段匹配关联
   • 当tests表的分组为C时，关联majors表中所有A和B组的专业
2. 排序规则：先按测试分数降序，再按专业分数降序，符合需求中的排序要求
3. 结果限制：最终通过LIMIT 5获取前5条符合条件的记录

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