MySQL5.5.20两表关联实现rank排名，解决ERROR1248报错问题

原SQL错误点 #

• 表关联语法错误：INNER JOIN后未指定要关联的学生表，且错误将分数表别名设为p后仍使用marks引用表
• 字段引用错误：分数表的分数字段为marks而非total，学生表不存在term字段，term属于分数表字段
• 变量重复初始化：代码开头与子查询中重复初始化排名变量，会导致排名计算逻辑混乱
• 派生表规则不满足：所有临时生成的派生表都需要设置别名，原代码中部分嵌套查询的别名逻辑缺失

正确可执行SQL #

SET @curRank := 0;
SELECT 
  s.id,
  s.adminno,
  s.fullname,
  m.term,
  m.year,
  m.marks,
  @curRank := @curRank + 1 AS `Rank`
FROM 
  Students s
INNER JOIN 
  Marks m ON s.adminno = m.adminno
WHERE 
  m.term = 'Term I' 
  AND m.year = 2021
ORDER BY 
  m.marks DESC;

如果需要实现同分同排名的逻辑，可以使用以下写法：

SET @curRank := 0, @prevMarks := NULL;
SELECT 
  s.id,
  s.adminno,
  s.fullname,
  m.term,
  m.year,
  m.marks,
  @curRank := IF(@prevMarks = m.marks, @curRank, @curRank + 1) AS `Rank`,
  @prevMarks := m.marks
FROM 
  Students s
INNER JOIN 
  Marks m ON s.adminno = m.adminno
WHERE 
  m.term = 'Term I' 
  AND m.year = 2021
ORDER BY 
  m.marks DESC;

逻辑说明 #

1. 通过adminno字段关联学生表和分数表，两张表分别设置别名s和m简化引用
2. 筛选条件作用于分数表的term和year字段，获取2021年第一学期的成绩数据
3. 按分数降序排序后，通过用户变量自增生成排名，第二种写法支持同分场景下排名保持一致

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