Я использую MYSQL для расчета TOP-N по каждому предмету в таблице ученика.

Question

У меня есть таблица ученика, в которой есть 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.

Примечание: я нашел этот запрос похожим на этот онлайн и просто адаптировал его под свои требования, это не моя работа.

Answer 1

Соединение просто необходимо, чтобы вы могли инициализировать переменные @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 строки, которые вы хотите.