我有一个学生表,里面有student_id、分数和科目
CREATE TABLE IF NOT EXISTS students
(student_id INT(3), subject ,VARCHAR(45), score INT(3) );
插入的数据是
insert into students values(1,'math',70);
insert into students values(1,'science',71);
insert into students values(1,'history',72);
insert into students values(1,'english',73);
insert into students values(1,'kannada',74);
insert into students values(3,'math',50);
insert into students values(3,'science',51);
insert into students values(3,'history',52);
insert into students values(3,'english',53);
insert into students values(3,'kannada',54);
insert into students values(2,'math',60);
insert into students values(2,'science',61);
insert into students values(2,'history',62);
insert into students values(2,'english',63);
insert into students values(2,'kannada',64);
在使用该查询后,我将获得所需的输出,
select student_id,score,subject
from
(select @prev := '', @n:=0) init
join
(select @n := if(subject != @prev , 1, @n+1) as n,
@prev := subject,
student_id,score,subject from students
order by
subject asc,
score desc
) x
where n<=2
order by subject, score desc;
我只是不明白这是如何工作的,为什么需要连接呢?这是子查询吗?from子句中的语句是否会在每一行数据上运行?有人能给我解释一下吗?我正在学习SQL。
注意:我在网上找到了这个类似的查询,我只是根据我的需求进行了调整,这不是我的工作。
发布于 2018-06-07 07:29:38
连接只是需要的,这样您就可以在查询中初始化变量@prev
和@n
。这需要与您试图过滤的查询分开来完成。您可以在查询之前执行此操作,但这样可以将所有内容放在一个自包含的查询中。
SET @prev = '';
SET @n = 0;
SELECT student_id, score, subject
FROM
(select @n := if(subject != @prev , 1, @n+1) as n,
@prev := subject,
student_id,score,subject from students
order by
subject asc,
score desc
) x
where n<=2
order by subject, score desc;
在本例中,使用子查询,以便您可以选择所需的n <= 2
行。
https://stackoverflow.com/questions/50730967
复制相似问题