MySQL讲义第49讲——select 查询之查询练习（七）：使用多种方法添加排名

MySQL讲义第49讲——select 查询之查询练习（七）：使用多种方法添加排名

MySQL 没有提供 RANK() 函数，要生成排名，可以使用变量来完成。查询需要使用四张表，分别是：student、teacher、course 和 electives，表结构如下：

CREATE TABLE student(
    s_id char(5) primary key,
    s_name char(20),
    birth datetime,
    phone char(20),
    addr varchar(100)
);

INSERT INTO student
VALUES('S2011','张晓刚','1999-12-3','13163735775','信阳市'),
('S2012','刘小青','1999-10-11','13603732255','新乡市'),
('S2013','曹梦德','1998-2-13','13853735522','郑州市'),
('S2014','刘艳','1998-6-24','13623735335','郑州市'),
('S2015','刘岩','1999-7-6','13813735225','信阳市'),
('S2016','刘若非','2000-8-31','13683735533','开封市'),
('S2021','董雯花','2000-7-30','13533735564','开封市'),
('S2022','周华建','1999-5-25','13243735578','郑州市'),
('S2023','特朗普','1999-6-21','13343735588','新乡市'),
('S2024','奥巴马','2000-10-17','13843735885','信阳市'),
('S2025','周健华','2000-8-22','13788736655','开封市'),
('S2026','张学有','1998-7-6','13743735566','郑州市'),
('S2031','李明博','1999-10-26','13643732222','郑州市'),
('S2032','达芬奇','1999-12-31','13043731234','郑州市');

CREATE TABLE teacher(
    t_id char(5) primary key,
    t_name char(20),
    job_title char(20),
    phone char(20)
);

INSERT INTO teacher
VALUES('T8001','欧阳修','教授','13703735666'),
('T8002','华罗庚','教授','13703735888'),
('T8003','钟南山','教授','13703735675'),
('T8004','钱学森','教授','13703735638'),
('T8005','李白','副教授','13703735828'),
('T8006','孔子','教授','13703735457'),
('T8007','王安石','副教授','13703735369');

CREATE TABLE course(
    c_id char(4) primary key,
    c_name char(20),
    t_id char(5)
);

INSERT INTO course
VALUES('C101','古代文学','T8001'),
('C102','高等数学','T8002'),
('C103','线性代数','T8002'),
('C104','临床医学','T8003'),
('C105','传染病学','T8003'),
('C106','大学物理','T8004'),
('C107','诗歌欣赏','T8005'),
('C108','教育学','T8006'),
('C109','刑事诉讼法','T8007'),
('C110','经济法','T8007');

CREATE TABLE score(
    s_id char(5),
    c_id char(4),
    score int,
    primary key(s_id, c_id)
);

INSERT INTO score
VALUES('S2011','C102',84),('S2011','C105',90),('S2011','C106',79),('S2011','C109',65),
('S2012','C101',67),('S2012','C102',52),('S2012','C103',55),('S2012','C104',86),
('S2012','C105',87),('S2012','C106',64),('S2012','C107',62),
('S2012','C108',73),('S2012','C109',78),('S2012','C110',89),
('S2013','C102',97),('S2013','C103',68),('S2013','C104',66),('S2013','C105',8),
('S2014','C102',90),('S2014','C103',85),('S2014','C104',77),('S2014','C105',96),
('S2015','C101',69),('S2015','C102',66),('S2015','C103',88),('S2015','C104',69),
('S2015','C105',66),('S2015','C106',88),('S2015','C107',69),
('S2015','C108',66),('S2015','C109',88),('S2015','C110',69),
('S2016','C101',65),('S2016','C102',84),('S2016','C107',82),('S2016','C108',56),
('S2021','C102',72),('S2021','C103',90),('S2021','C104',90),('S2021','C105',57),
('S2022','C102',97),('S2022','C103',93),('S2022','C109',47),('S2022','C110',62),
('S2023','C102',68),('S2023','C103',86),('S2023','C109',56),('S2023','C110',91),
('S2024','C102',87),('S2024','C103',97),('S2024','C109',80),('S2024','C110',81),
('S2025','C102',61),('S2025','C105',62),('S2025','C106',87),('S2025','C109',82),
('S2026','C102',84),('S2026','C105',48),('S2026','C106',90),('S2026','C109',73);

一、使用变量生成排名

1、不考虑重复数据

查询课程【高等数学】成绩，按成绩高低进行排序，并显示排名。如果不考虑排名时的重复数据，则使用以下的命令：

SET @rank = 0;
SELECT
    s.s_id,
    s.s_name,
    sc.score,
    @rank := @rank +1 AS rank
FROM
    score sc JOIN student s
    ON sc.s_id = s.s_id
WHERE
    sc.c_id = (
        SELECT c_id
        FROM course
        WHERE c_name = '高等数学')
ORDER BY
    sc.score DESC
;
+-------+-----------+-------+------+
| s_id  | s_name    | score | rank |
+-------+-----------+-------+------+
| S2013 | 曹梦德    |    97 |    1 |
| S2022 | 周华建    |    97 |    2 |
| S2014 | 刘艳      |    90 |    3 |
| S2024 | 奥巴马    |    87 |    4 |
| S2011 | 张晓刚    |    84 |    5 |
| S2016 | 刘若非    |    84 |    6 |
| S2026 | 张学有    |    84 |    7 |
| S2021 | 董雯花    |    72 |    8 |
| S2023 | 特朗普    |    68 |    9 |
| S2015 | 刘岩      |    66 |   10 |
| S2025 | 周健华    |    61 |   11 |
| S2012 | 刘小青    |    52 |   12 |
+-------+-----------+-------+------+
12 rows in set (0.01 sec)

---#############  在 FROM 子句中定义变量并赋值 ##########################
SELECT
    s.s_id,
    s.s_name,
    sc.score,
    @rank := @rank +1 AS rank
FROM
    score sc JOIN student s
    ON sc.s_id = s.s_id ,(SELECT @rank := 0) rank
WHERE
    sc.c_id = (
        SELECT c_id
        FROM course
        WHERE c_name = '高等数学')
ORDER BY
    sc.score DESC
;
+-------+-----------+-------+------+
| s_id  | s_name    | score | rank |
+-------+-----------+-------+------+
| S2013 | 曹梦德    |    97 |    1 |
| S2022 | 周华建    |    97 |    2 |
| S2014 | 刘艳      |    90 |    3 |
| S2024 | 奥巴马    |    87 |    4 |
| S2011 | 张晓刚    |    84 |    5 |
| S2016 | 刘若非    |    84 |    6 |
| S2026 | 张学有    |    84 |    7 |
| S2021 | 董雯花    |    72 |    8 |
| S2023 | 特朗普    |    68 |    9 |
| S2015 | 刘岩      |    66 |   10 |
| S2025 | 周健华    |    61 |   11 |
| S2012 | 刘小青    |    52 |   12 |
+-------+-----------+-------+------+
12 rows in set (0.02 sec)

2、重复数据生成并列排名

查询课程【高等数学】成绩，按成绩高低进行排序，并显示排名。如果考虑排名时的重复数据，则使用以下的命令：

--说明：使用变量 @sc 保存上一条记录的成绩，如果与上一条记录相同，则名次不增加，如果不相同，
--      则把当前记录的成绩赋给变量，同时名次加 1
SELECT
    s.s_id,
    s.s_name,
    sc.score,
    @rank := @rank + (CASE WHEN @sc = sc.score THEN 0 
                          WHEN @sc :=sc.score THEN 1                          
                     END) AS rank
FROM
    score sc JOIN student s
    ON sc.s_id = s.s_id ,(SELECT @rank := 0) rank, (SELECT @sc := null) sc_1
WHERE
    sc.c_id = (
        SELECT c_id
        FROM course
        WHERE c_name = '高等数学')
ORDER BY
    sc.score DESC
;
+-------+-----------+-------+------+
| s_id  | s_name    | score | rank |
+-------+-----------+-------+------+
| S2013 | 曹梦德    |    97 |    1 |
| S2022 | 周华建    |    97 |    1 |
| S2014 | 刘艳      |    90 |    2 |
| S2024 | 奥巴马    |    87 |    3 |
| S2011 | 张晓刚    |    84 |    4 |
| S2016 | 刘若非    |    84 |    4 |
| S2026 | 张学有    |    84 |    4 |
| S2021 | 董雯花    |    72 |    5 |
| S2023 | 特朗普    |    68 |    6 |
| S2015 | 刘岩      |    66 |    7 |
| S2025 | 周健华    |    61 |    8 |
| S2012 | 刘小青    |    52 |    9 |
+-------+-----------+-------+------+
12 rows in set (0.00 sec)

3、重复数据生成并列排名，同时重复的数据也占用名次

SELECT
    @index := @index + 1 AS idx,
    s.s_id,
    s.s_name,
    sc.score,
    @rank := (CASE WHEN @sc = sc.score THEN @rank
                   WHEN @sc :=sc.score THEN @index
              END) AS rank
FROM
    score sc JOIN student s
    ON sc.s_id = s.s_id ,(SELECT @rank := 0) rank, 
    (SELECT @sc := null) sc_1, (SELECT @index := 0) idx
WHERE
    sc.c_id = (
        SELECT c_id
        FROM course
        WHERE c_name = '高等数学')
ORDER BY
    sc.score DESC
;
+------+-------+-----------+-------+------+
| idx  | s_id  | s_name    | score | rank |
+------+-------+-----------+-------+------+
|    1 | S2013 | 曹梦德    |    97 | 1    |
|    2 | S2022 | 周华建    |    97 | 1    |
|    3 | S2014 | 刘艳      |    90 | 3    |
|    4 | S2024 | 奥巴马    |    87 | 4    |
|    5 | S2011 | 张晓刚    |    84 | 5    |
|    6 | S2016 | 刘若非    |    84 | 5    |
|    7 | S2026 | 张学有    |    84 | 5    |
|    8 | S2021 | 董雯花    |    72 | 8    |
|    9 | S2023 | 特朗普    |    68 | 9    |
|   10 | S2015 | 刘岩      |    66 | 10   |
|   11 | S2025 | 周健华    |    61 | 11   |
|   12 | S2012 | 刘小青    |    52 | 12   |
+------+-------+-----------+-------+------+
12 rows in set (0.00 sec)

二、使用自连接

1、重复数据生成并列排名

SELECT
    sc.s_id,
    s.s_name,
    sc.score,
    (SELECT COUNT(*) + 1
     FROM (SELECT DISTINCT score
           FROM score
           WHERE c_id = @c_id) sc_distinct
     WHERE sc_distinct.score > sc.score
    ) AS rank
FROM
    score sc JOIN student s
    ON sc.s_id = s.s_id,
    (SELECT @c_id = (
        SELECT c_id
        FROM course
        WHERE c_name = '高等数学')
    ) gdsx
WHERE
    sc.c_id = @c_id
ORDER BY
    sc.score DESC
;
+-------+-----------+-------+------+
| s_id  | s_name    | score | rank |
+-------+-----------+-------+------+
| S2013 | 曹梦德    |    97 |    1 |
| S2022 | 周华建    |    97 |    1 |
| S2014 | 刘艳      |    90 |    2 |
| S2024 | 奥巴马    |    87 |    3 |
| S2011 | 张晓刚    |    84 |    4 |
| S2016 | 刘若非    |    84 |    4 |
| S2026 | 张学有    |    84 |    4 |
| S2021 | 董雯花    |    72 |    5 |
| S2023 | 特朗普    |    68 |    6 |
| S2015 | 刘岩      |    66 |    7 |
| S2025 | 周健华    |    61 |    8 |
| S2012 | 刘小青    |    52 |    9 |
+-------+-----------+-------+------+
12 rows in set (0.01 sec)

2、重复数据生成并列排名，同时重复的数据也占用名次

--###########  rank 列统计比当前成绩大的成绩的数量，加 1 得到名次  #################
SELECT
    sc.s_id,
    s.s_name,
    sc.score,
    (SELECT COUNT(*) + 1
     FROM score 
     WHERE c_id = @c_id AND score > sc.score
    ) AS rank
FROM
    score sc JOIN student s
    ON sc.s_id = s.s_id,
    (SELECT @c_id = (
        SELECT c_id
        FROM course
        WHERE c_name = '高等数学')
    ) gdsx
WHERE
    sc.c_id = @c_id
ORDER BY
    sc.score DESC
;
+-------+-----------+-------+------+
| s_id  | s_name    | score | rank |
+-------+-----------+-------+------+
| S2013 | 曹梦德    |    97 |    1 |
| S2022 | 周华建    |    97 |    1 |
| S2014 | 刘艳      |    90 |    3 |
| S2024 | 奥巴马    |    87 |    4 |
| S2011 | 张晓刚    |    84 |    5 |
| S2016 | 刘若非    |    84 |    5 |
| S2026 | 张学有    |    84 |    5 |
| S2021 | 董雯花    |    72 |    8 |
| S2023 | 特朗普    |    68 |    9 |
| S2015 | 刘岩      |    66 |   10 |
| S2025 | 周健华    |    61 |   11 |
| S2012 | 刘小青    |    52 |   12 |
+-------+-----------+-------+------+
12 rows in set (0.01 sec)