postgre如何获得一个包含前 5 个列表的列表，然后将所有其他列表作为 SQL 中的第 6 个项目？

Question

所以我有一个表，每次球队进球时都会存储一行：

| id | time |  team  | level |
------------------------------
|  1 | #### | Team 1 |   A   |
|  2 | #### | Team 2 |   B   |
...

我正在努力让排名前五的球队进入A级，进球最多，然后将其他球队合并为名单中的第六项，如下所示：

|   team  |  goals |
-------------------
|  Team 1 |   13   |
|  Team 5 |   12   |
| Team 34 |    9   |
|  Team 7 |    7   |
| Team 19 |    7   |
|  Other  |   54   |

我该怎么得到这个？我试过这样的东西：

SELECT team, goals
FROM (
SELECT team, COALESCE(SUM(CASE WHEN level = 'A' THEN 1 ELSE 0 END), 0) AS goals 
FROM goals
WHERE level = A
GROUP BY team
ORDER BY goals DESC
LIMIT 5
) AS g
UNION ALL

所以这给了我前5名，但我不知道如何制作第二部分，即"；"其他"；，因为它必须将球队排除在前5名之外…

Answer 1

还研究了排名前五的球队进球数相等的情况。参见函数ROW_NUMBER((、RANK((和DENSE_RANK((

SELECT team
, SUM(goal_total) goal_total
FROM
(
SELECT CASE
WHEN ROW_NUMBER()
OVER (ORDER BY COUNT(*) desc) <=5
THEN team
ELSE 'Other'
END
AS team
, CASE
WHEN ROW_NUMBER()
OVER (ORDER BY COUNT(*) desc) <=5
THEN ROW_NUMBER()
OVER (ORDER BY COUNT(*) desc)
ELSE 6
END
AS place
, COUNT(*)
AS goal_total
FROM goals
WHERE level = 'A'
GROUP 
BY team
)
GROUP
BY team
, place
ORDER
BY place

Answer 2

使用ROW_NUMBER对团队进行排名。组使用CASE WHEN：

SELECT CASE WHEN rn <= 5 THEN team ELSE 'others' END AS team, sum(goals) as goals
FROM (
SELECT 
team, 
COUNT(*) AS goals,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
FROM goals
WHERE level = 'A'
GROUP BY team
) AS g
GROUP BY CASE WHEN rn <= 5 THEN team ELSE 'others' END
ORDER BY MAX(rn); 

Answer 3

试试这个，

create table #temp(team varchar(50),goal int)
insert into #temp 
SELECT team, COALESCE(SUM(CASE WHEN level = 'A' THEN 1 ELSE 0 END), 0) AS goals 
FROM goals
WHERE level = A
GROUP BY team
ORDER BY goals DESC
LIMIT 5
select team,goal from #temp
union all
SELECT team, COALESCE(SUM(CASE WHEN level = 'A' THEN 1 ELSE 0 END), 0) AS goals 
FROM goals g
WHERE level = A
and not exists (select 1 from #temp t where g.team=t.team)
GROUP BY team
--ORDER BY goals DESC
drop table #temp

Answer 4

将聚合和窗口函数结合起来，可能会短一点

SELECT case rn when 6 then 'others' else team end gteam, sum(goals) goals
FROM (
SELECT team, count(*) goals, 
least(6, row_number() over(order by count(*))) rn
FROM goals
WHERE level = A
GROUP BY team
) t
GROUP BY gteam, rn
ORDER BY rn