sql - 从每组返回一行

发布于 2024-10-07 21:27:45 字数 414 浏览 1 评论 0原文

考虑下表，只返回球队成员资格及其相关球员，但每支球队只返回一名球员的最佳方法是什么？需要注意的重要部分是，选择需要基于一组给定的玩家 ID。这就是起点。

使用下面的示例，我将拥有玩家 ID：1,2,3（以及其他），而我最终需要的是用户 ID 列表中的唯一成员身份（在本例中为 1,2,3）。我希望最终获得 ID 为 1 和 1 的 team_memberships 。 2

谢谢！

player
id | name
1  | bob
2  | joe
3  | tom

team_memberships
id | team_id | player_id
1  | 1       | 1
2  | 2       | 2
3  | 1       | 3

team
id | name
1  | jets
2  | kings

原文

Considering the tables below, what is the best way to only return the team membership and its related player, but only one player from each team? The important part to note is that selection needs to be based on a given set of player ids. That is the starting point.

Using the example below I would have the player ids: 1,2,3 (among others) and what I need to end up with is the unique memberships from a list of user ids(1,2,3 in this case). I would want to end up with team_memberships with id 1 & 2

Thanks!

player
id | name
1  | bob
2  | joe
3  | tom

team_memberships
id | team_id | player_id
1  | 1       | 1
2  | 2       | 2
3  | 1       | 3

team
id | name
1  | jets
2  | kings

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小嗲 2024-10-14 21:27:45

您的意思是

选择 team_id, Min(player_id) 来自团队_会员资格其中player_id在(1,2,3) Group By team_id

如果选择的球员需要基于球员表中的某些其他属性，则：

Select team_id, 
   (Select Min(player_id) -- in case more than one player satisfies criterion
    From players 
    where playerId = m.Player_Id
       And [Some other ctiterion]) playerId
From team_Memberships m
Where player_id In (1,2,3)  
Group By team_id

Do you mean

Select team_id, Min(player_id) From team_Memberships Where player_id In (1,2,3) Group By team_id

If the player selected needs to be based on some other attribute in the players table, then:

Select team_id, 
   (Select Min(player_id) -- in case more than one player satisfies criterion
    From players 
    where playerId = m.Player_Id
       And [Some other ctiterion]) playerId
From team_Memberships m
Where player_id In (1,2,3)  
Group By team_id

回复收藏 0 原文

~没有更多了~