每个赛季都有顶级球队(只有一支)

gwbalxhn 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(290)

我只想要每个赛季获胜最多的顶级球队。

我想要我的结果集为赛季：1900队名称：“a”wins:4 and 就像1901年的球队：“b”wins:5
如果有两支球队赢得相同的胜利，那么我的查询应该按照字母顺序选择第一支，就像在我的例子中，它将选择1900赛季的球队：“a”
源查询

create table #teams( season varchar(100), team_name varchar(100),win int);

insert #teams (season , team_name ,win)
select '1900','a' , 4;

insert #teams (season , team_name ,win)
select '1900','b' , 4;

insert #teams (season , team_name ,win)
select '1900','c' , 2;

insert #teams (season , team_name ,win)
select '1901','a' , 3;

insert #teams (season , team_name ,win)
select '1901','b' , 5;

insert #teams (season , team_name ,win)
select '1901','c' , 5;

select * from #teams;

select season ,team_name , max(win) as wins
from #teams
group by season, team_name
order by season

sql sql-server Database

来源：https://stackoverflow.com/questions/63176999/having-top-team-of-every-season-only-one

2条答案

按热度按时间

5vf7fwbs1#

以下操作应该有效。。
我是按win desc的编号排序的，也按球队名称asc排序的，这应该符合选择在最终名单中按字母顺序排列的优秀球队的条件。

select * from (
select season 
      ,team_name 
      ,win
      ,row_number() over(partition by season order by win desc,team_name asc) as rnk
  from teams
    )x
where x.rnk=1 

+--------+-----------+-----+-----+
| season | team_name | win | rnk |
+--------+-----------+-----+-----+
|   1900 | a         |   4 |   1 |
|   1901 | b         |   5 |   1 |
+--------+-----------+-----+-----+

全分贝小提琴链接
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=abd425477e893e3dd869467daab4f216

赞(0）回复(0）举报 2021-07-27

suzh9iv82#

有几种方法：

WITH CTE AS(
    SELECT season,
           team_name,
           wins,
           ROW_NUMBER() OVER (PARTITION BY Season ORDER BY wins DESC, team_name ASC) AS RN
    FROM dbo.YourTable)
SELECT season,
       team_name,
       win
FROM CTE
WHERE ROW_NUMBER() = 1;

SELECT TOP (1) WITH TIES
       season,
       team_name,
       wins
FROM dbo.YourTable
ORDER BY DENSE_RANK() OVER (PARTITION BY Season ORDER BY wins DESC);
``` `DENSE_RANK` 将返回所有获胜最多的球队，其中 `ROW_NUMBER` 将按字母顺序返回一队（由于中的附加值 `ORDER BY` .

赞(0）回复(0）举报 2021-07-27

我来回答

每个赛季都有顶级球队(只有一支)

2条答案

相关问题

热门标签

最新问答