Поиск строки, соответствующей N-му значению в каждой группе, в SQL
У меня есть таблица A следующим образом:
name course grade
Bob C1 12
Bob C2 13
Bob C3 23
Bob C4 17
James C2 15
James C6 27
Nick C5 18
Nick C1 16
Nick C3 22
Nick C2 32
Nick C7 19
Мне нужен запрос, который может вернуть имя учащегося и номер курса для каждого учащегося, соответствующего 3-й наивысшей оценке этого учащегося. Например, в приведенной выше таблице 3-я высшая оценка для Боба — 17, поэтому номер курса — C4. Кроме того, поскольку у Джеймса менее 3 курсов, его курс не следует включать в результат запроса. Другими словами, результат запроса должен показать это:
name course_id
Bob C4
Nick C7
Какой запрос может это сделать?
@a_horse_with_no_name допустим, это MySQL
Ответы 3
используйте коррелированный подзапрос и аналитическую функцию row_number, которые поддерживают большинство СУБД
with cte as
( select t1.* from table t1 where
exists ( select 1 from table t2 where t1.name=t2.name
group by name
having count(distinct course)>=3
)
) ,
cte2 as
(
select * ,row_number() over(partition by name order by grade asc) rn
from cte
) select * from cte2 where rn=3
В стандартном SQL это можно сделать с помощью оконных функций:
select name, course
from (
select name, course, grade,
dense_rank() over (partition by name order by grade) as rnk
from grades
) t
where rnk = 3
Онлайн-пример: https://rextester.com/YOHZB50840
Онлайн-пример с MySQL: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3f9ff551c89f6941dbf8e424e6dfda56
Не могли бы вы использовать свой код с помощью онлайн-редактора sql. Я попытался использовать эту ссылку, и это дало мне ошибку: sqltest.net
@JamesRobisnon: тогда, видимо, ваша СУБД не поддерживает современный SQL. Я добавил онлайн-пример
Вы можете попробовать использовать row_number()
select name, course
from (
select name, course, grade,
row_number() over (partition by name order by grade desc) as rn
from grades
) t where rn = 3
А если есть связи? Ваш вопрос не ясен.