mysql - 使用 MySQL 变量按评分创建组

Question

在 MySQL 5.6 服务器上我有这张表:

CREATE TABLE `student` (
    `course` INT(5) NULL DEFAULT NULL,
    `course_desc` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `vote` INT(2) NULL DEFAULT NULL,
    UNIQUE INDEX `course_name` (`course`, `name`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

有数据:

INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (100, 'Math', 'Mario', 10);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (100, 'Math', 'Giovanna', 8);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (100, 'Math', 'Federico', 8);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (100, 'Math', 'Arianna', 5);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (200, 'History', 'Mario', 9);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (200, 'History', 'Giovanna', 7);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (200, 'History', 'Patrizio', 3);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (200, 'History', 'Teresa', 10);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (300, 'Literacy', 'Giovanna', 7);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (300, 'Literacy', 'Federico', 6);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (300, 'Literacy', 'Arianna', 10);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (400, 'Science', 'Giovanni', 9);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (400, 'Science', 'Giovanna', 7);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (400, 'Science', 'Maria', 9);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (400, 'Science', 'Teresa', 0);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (400, 'Science', 'Carlo', 7);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (400, 'Science', 'Federico', 6);
INSERT INTO `student` (`course`, `course_desc`, `name`, `vote`) VALUES (500, 'Philosophy', 'Maria', 10);

这个查询:

SELECT 
    (@id := @id + 1) AS "ID", 
    t1.`course` AS "COURSE", 
    t1.`course_desc` AS "COURSE_DESC", 
    t1.`name` AS "NAME", 
    t1.`vote` AS "VOTE", 
    CASE 
        WHEN @prev_course = t1.`course` THEN 
            (
                CASE 
                    WHEN @prev_vote = t1.`vote` THEN @rank_count 
                    WHEN @prev_vote := t1.`vote` THEN @rank_count := @rank_count + 1 
                END 
            )
        WHEN @prev_course := t1.`course` THEN 
            (
                @rank_count := 1
            )
    END AS "RANK" 
FROM 
    (SELECT @id := 0) AS t0, 
    `student` AS t1, 
    (SELECT @prev_course := NULL) AS t2, 
    (SELECT @prev_vote := NULL) AS t3, 
    (SELECT @rank_count := 0) AS t4 
ORDER BY 
    t1.`course`, 
    t1.`vote` DESC;

产生错误的结果:

------------------------------------------------------------------
ID  COURSE  COURSE_DESC NAME        VOTE    RANK
------------------------------------------------------------------
1   100     Math        Mario       10      1
2   100     Math        Giovanna    8       2
3   100     Math        Federico    8       2
4   100     Math        Arianna     5       3
5   200     History     Teresa      10      1
6   200     History     Mario       9       2
7   200     History     Giovanna    7       3
8   200     History     Patrizio    3       4
9   300     Literacy    Arianna     10      1
10  300     Literacy    Giovanna    7       2
11  300     Literacy    Federico    6       3
12  400     Science     Giovanni    9       1
13  400     Science     Maria       9       2
14  400     Science     Giovanna    7       3
15  400     Science     Carlo       7       3
16  400     Science     Federico    6       4
17  400     Science     Teresa      0       NULL
18  500     Philosophy  Maria       10      1
------------------------------------------------------------------

这个其他查询:

SELECT 
    (@id := @id + 1) AS "ID", 
    t1.`course` AS "COURSE", 
    t1.`course_desc` AS "COURSE_DESC", 
    t1.`name` AS "NAME", 
    t1.`vote` AS "VOTE", 
    CASE 
        WHEN @prev_course = t1.`course_desc` THEN 
            (
                CASE 
                    WHEN @prev_vote = t1.`vote` THEN @rank_count 
                    WHEN @prev_vote := t1.`vote` THEN @rank_count := @rank_count + 1 
                END 
            )
        WHEN @prev_course := t1.`course_desc` THEN 
            (
                @rank_count := 1
            )
    END AS "RANK" 
FROM 
    (SELECT @id := 0) AS t0, 
    `student` AS t1, 
    (SELECT @prev_course := NULL) AS t2, 
    (SELECT @prev_vote := NULL) AS t3, 
    (SELECT @rank_count := 0) AS t4 
ORDER BY 
    t1.`course`, 
    t1.`vote` DESC;

产生一个非常错误的结果

------------------------------------------------------------------
ID  COURSE  COURSE_DESC NAME        VOTE   RANK
------------------------------------------------------------------
1   100     Math        Mario       10     NULL
2   100     Math        Giovanna    8      1
3   100     Math        Federico    8      1
4   100     Math        Arianna     5      2
5   200     History     Teresa      10     3
6   200     History     Mario       9      4
7   200     History     Giovanna    7      5
8   200     History     Patrizio    3      6
9   300     Literacy    Arianna     10     7
10  300     Literacy    Giovanna    7      8
11  300     Literacy    Federico    6      9
12  400     Science     Giovanni    9      10
13  400     Science     Maria       9      10
14  400     Science     Giovanna    7      11
15  400     Science     Carlo       7      11
16  400     Science     Federico    6      12
17  400     Science     Teresa      0      NULL
18  500     Philosophy  Maria       10     13
------------------------------------------------------------------

目标是根据 vote 值从顶部 (1) 到底部 (n) 对表进行排名。相同的投票 = 相同的排名。按类(class)分组。我需要一些帮助非常感谢

先生

Answer 1

你的问题有点不清楚你想要什么。但是你对变量的使用是错误的。您不应该在一个表达式中分配一个变量，然后在另一个表达式中引用它。 MySQL 不保证 select 中表达式的求值顺序，因此它们可能会以错误的顺序求值。

我想你想要这样的东西:

select s.*,
       (@rn := if(@c = course_desc, @rn + 1,
                  if(@c := course_desc, 1, 1)
                 )
       ) as rank
from (select s.*
      from student s
      order by s.course_desc, s.vote desc
     ) s cross join
     (select @c := '', @rn := 0) params;

如果想让投票相同的学生有相同的值:

select s.*,
       (@rn := if(@cv = concat_ws(':', course_desc, vote), @rn,
                  if(@cv like concat(course_desc, ':%'),
                     if(@cv := concat_ws(':', course_desc, vote), @rn + 1, @rn + 1),
                     if(@cv := concat_ws(':', course_desc, vote), 1, 1)
                    )
                 )
       ) as rank
from (select s.*
      from student s
      order by s.course_desc, s.vote desc
     ) s cross join
     (select @cv := '', @rn := 0) params