mysql组内排序并展示组内行号

1.概述

显示班级内排名

2.DDL

CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  school INT,
  clazz INT,
  score INT
);

3.数据

SELECT 
    *
FROM student

4. 获得排名

SELECT 
    @rowno:=
    CASE WHEN @school=s.school AND @clazz=s.clazz THEN @rowno + 1
    ELSE 1
    END AS rowno,
    @school := s.school AS temp1,
    @clazz := s.clazz AS temp2,
    id,
    school,
    clazz,
    score
FROM 
(
    SELECT 
        *
    FROM student 
    ORDER BY school, clazz, score DESC 
) AS s,
(SELECT @rowno:=0, @school:=0, @clazz) AS t