在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( 排名。 相同的投票=相同的排名。 按课程分组。 我需要一些帮助 很多
先生
你的问题有点不清楚你想要什么。 但是你对变量的使用是错误的。 不应在一个表达式中赋值变量,然后在另一个表达式中引用该变量。 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
这是使用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。
在这里演示