Oracle SQL:按“类别"获得前3个结果 [英] Oracle SQL: Get top 3 results per "category"

问题描述

我有一个包含多个运动队和运动项目的Oracle数据库表.现在，我需要为每个运动项目分配最低ID的3个团队(如果少于3个，则更少)(基于他们的ID).

I have an Oracle-db-table with multiple sportsteams and sports. Now I need to get the 3 teams (Or less if less than 3 teams) with the lowest id for each sport (based on their id).

(在我的例子中，足球:巴塞罗那，皇家马德里，PSG/曲棍球:纽约岛民，纳什维尔掠食者，波士顿熊/篮球:洛杉矶湖人队，波士顿凯尔特人队，克利夫兰骑士队/棒球:纽约洋基队)

(In my example Soccer: Barcelona, Real Madrid, PSG / Hockey: NY Islanders, Nashville Predators, Boston Bruins / Basketball: LA Lakers, Boston Celtics, Cleveland Cavaliers / Baseball: NY Yankees)

我的表格/条目:

Create Table Teams(
teamid NUMBER,
teamname VARCHAR(20),
Sport VARCHAR(20)
);



INSERT INTO Teams (teamid, teamname, Sport) values (1, 'Barcelona', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (2, 'Real Madrid', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (3, 'PSG', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (4, 'Liverpool', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (5, 'Bayern Munich', 'Soccer');

INSERT INTO Teams (teamid, teamname, Sport) values (6, 'NY Islanders', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (7, 'Nashville Predators', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (8, 'Boston Bruins', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (9, 'Vancouver Canucks', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (10, 'NY Rangers', 'Hockey');

INSERT INTO Teams (teamid, teamname, Sport) values (11, 'LA Lakers', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (12, 'Boston Celtics', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (13, 'Cleveland Cavaliers', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (14, 'Huston Rockets', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (15, 'Chicago Bulls', 'Basketball');

INSERT INTO Teams (teamid, teamname, Sport) values (16, 'NY Yankees', 'Baseball'); 

我已经尝试过:获得每个小组的最佳成绩(在Oracle中)，但我仍然拥有所有团队.

I have already tried this: Get top results for each group (in Oracle) but i still get all the teams.

基于示例的我的选择语句":

My Select Statement based on the example:

SELECT t.teamname, t.sport 
FROM teams t
  LEFT OUTER JOIN teams t2 
    ON (t.teamname = t2.teamname AND t.teamid <= t2.teamid) 
GROUP BY t.teamid, t.teamname, t.sport
HAVING COUNT(*) <= 3 
ORDER BY t.sport asc;

推荐答案

一个选项使用 ROW_NUMBER :

SELECT teamid, teamname, Sport
FROM
(
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Sport ORDER BY teamid) rn
    FROM Teams t
) s
WHERE rn <= 3
ORDER BY Sport, teamid;

这篇关于Oracle SQL:按“类别"获得前3个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！