使用mysql变量通过评分创建组

yc0p9oo0  于 2021-06-23  发布在  Mysql
关注(0)|答案(2)|浏览(363)

在MySQL5.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 价值观。同一票=同一级别。按课程分组。我很需要帮助
先生

sqlmysqlvariablesscoring

2条答案

按热度按时间
z4iuyo4d

z4iuyo4d1#

这是另一种使用 LEFT JOIN 而不是变量：

SELECT s1.course, s1.course_desc, s1.name, s1.vote, COUNT(s2.name) + 1 AS rank
FROM student AS s1
LEFT JOIN student AS s2 
   ON s1.course = s2.course AND s1.name <> s2.name AND s1.vote < s2.vote
GROUP BY s1.course, s1.course_desc, s1.name, s1.vote  
ORDER BY s1.course, rank

该查询假设每个学生都由其姓名唯一标识。在真实场景中，您可以用id替换名称。
此处演示

赞(0）分享  回复(0）举报  2021-06-23
ljsrvy3e

ljsrvy3e2#

你的问题有点不清楚你想要什么。但是你使用变量是错误的。您不应该在一个表达式中指定一个变量，然后在另一个表达式中引用它。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
赞(0）分享  回复(0）举报  2021-06-23
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com