Sql-запрос, чтобы найти строку с максимальной суммой для столбца
У меня есть следующая таблица «Очки», в которой указаны результаты игроков за определенный год.
Sid Name Score Year
1 John 500 2016
2 Kim 900 2015
3 Ren 300 2016
4 John 600 2015
5 Kim 200 2016
6 Ren 200 2016
Найдите игрока, который забил максимальное количество пробежек в 2016 году
Я могу найти это, используя запрос ниже
Select Name
from
( select Name
, sum(Score) as sumScore
from Scores
where year=2016
group
by Name
) sub
order
by sumScore desc
limit 1;
Выход: Ren
Как я могу найти то же самое, не используя заказ?
Я пробовал ниже, но он не работает, поскольку он не может ссылаться на подпункт во втором, где предложение и жалуется, что подпункт отношения не существует
select Name from(select Name,sum(Score) as sumScore from Scores
where year=2016 group by Name)sub where sumScore=(select max(sumScore) from sub)
Ответы 4
Вы можете попробовать использовать коррелированный подзапрос
select * from tablename a where score in
(select max(score) from tablename b where a.year=b.year and b.year=2016)
and a.year=2016
ИЛИ вы можете использовать оконную функцию row_number (), как показано ниже
select * from
(
select *,row_number() over(partition by yr order by score desc) as rn from cte1
)a where rn=1 and yr=2016
ВЫХОД:
id name score yr
1 John 500 2016
в ваших данных примера и ren, и john набрали 500 баллов после агрегирования за 2016 год, так что в этом случае логика выбора Ren @ user3580455
SELECT Scores.Name, SUM(Scores.Score)
FROM (
select Name,sum(Score) as sumScore, Years
from Scores
where Years=2016
group by Name, Years
)sub INNER JOIN Scores ON sub.Name = Scores.Name
GROUP BY Scores.Name
HAVING SUM(Scores.Score) = MAX(sub.sumScore)
Вы также можете использовать общее табличное выражение в сочетании с плотный ранг
with cte as (
select *,
DENSE_RANK() OVER(ORDER BY score desc, year) rank
from demo
where year = 2016
)
select *
from cte
where rank = 1
Отредактируйте, чтобы получить игроков с максимальным счетом 2016, вы можете настроить вышеуказанный запрос как
with cte as (
select name,year ,
DENSE_RANK() OVER(ORDER BY sum(score) desc, year) rank
from demo
where year = 2016
group by name,year
)
select *
from cte
where rank = 1
Зачем вам использовать CTE? После того, как вы ввели оконные функции, вы уже решили проблему. CTE просто слишком усложняет ситуацию.
Один простой метод использует оконные функции:
select s.*
from (select s.*, max(s.score) over (partition by year) as max_score
from scores s
where year = 2016
) s
where score = max_score;
Я обновил вопрос выводом, в результате запроса будет выбран игрок, у которого больше всего совокупных очков, тогда как это даст игроку с максимальным количеством очков.