请帮我写SQl查询 [英] Please help me writing SQl Query

问题描述

在此查询中，您必须列出一对玩家ID和玩家名称的球员，他们为同一支球队效力。如果一名球员为3支球队效力，则另一名玩家需要支付
才能参加比赛。同样的3支队伍。不能少，不多了。如果两名球员目前不参加任何球队，他们也应该被包括在内。查询应该返回

（playerID1，playername1，playerID2，playerName2），没有重复，例如如果玩家1信息在玩家2之前出现，则不应该有另一个玩家2的元组
$ b玩家1之前的$ b信息。



例如，如果玩家A玩CSK和MI，而玩家b玩CSK，MI和DD你不应该得到他们。他们都必须为CSK和MI而不是其他人。







先决条件：

表：

player（playerID：integer，playerName：string）

team（teamID：integer，teamName：string，sport：string）

播放（playerID：整数，teamID：整数）

In this Query, you have to list pair of players with their playerID and playerName who play for the exact same teams.If a player plays for 3 teams, the other has
to play for exact same 3 teams. No less, no more. If two players currently do not play for any team, they should also be included. The query should return
(playerID1, playername1, playerID2, playerName2) with no repetition such as if player 1 info comes before player 2, there should not be another tuple with player 2
info coming before player 1.

For example if player A plays for CSK and MI, and player b plays for CSK, MI, and DD you should not get them. They both have to play for CSK, and MI and no one else.



Prerequisites:
Tables:
player(playerID: integer, playerName: string)
team(teamID: integer, teamName: string, sport: string)
plays(playerID: integer, teamID: integer)

Example data:
PLAYER    
playerID    playerName
1           Dravid
2           Sachin
3           Dhoni
4           Yuvraj
5           Ponting

TEAM      
teamID     teamName    sport
1          CSK        CRICKET
2          DD         CRICKET
3          MI         CRICKET
4          RR         CRICKET
5          KKR        CRICKET

PLAYS
playerID    TeamID
1           1
1           2
1           3
2           1
2           3
3           1
3           3

所以你应该得到这个答案 -

2，SACHIN，3，DHONI

4，YUVRAJ，5，PONTING



2，SACHIN，3 DHONI是回答，因为他们都只参加CSK和MI 4，Yuvraj，5，Ponting是一个答案因为两个球员都没有参加任何球队比赛应该是

的输出。









我写的查询最多

So you should get this as answer-
2, SACHIN, 3, DHONI
4, YUVRAJ, 5, PONTING

2, SACHIN, 3 DHONI is answer because both play for exclusively CSK and MI 4, Yuvraj, 5, Ponting is an answer because both players play for no teams which should be
in output.




I have written query upto

select distinct player.playerid,player.playername,plays.teamid,team.teamname,team.sport from player  left outer
join plays on player.playerid=plays.playerid
  left outer join team on plays.teamid = team.teamid where plays.teamid is null

推荐答案

select distinct 
       player.playerid,player.playername,plays.teamid,team.teamname,team.sport 
from 
       player 
left outerjoin plays on player.playerid=plays.playerid
left outer join team on plays.teamid = team.teamid where plays.teamid is null and player.playerid NOT IN (select distinct playerid from plays)

我不确定我是否理解你，但是，检查这个 SqlFiddle [ ^ ]。

I'm not sure i understand you well, but, check this SqlFiddle[^].

如果我理解正确，那么我的想法是这样的：

1。使用 XML PATH将玩家所属的队友连接到一个单元格中（） [ ^ ]。

2.然后，选择任何拥有多个玩家的玩家或不属于任何团队的玩家。

试试这个：

If I understand you correctly, then my idea is like this:
1. Concatenate the teamid's that a player belongs to into a cell using For XML PATH()[^].
2. Then, select players from any team that have more than one player or players that do not belong to any team.
Try this:

with cte1 (playerid, teamids) as
(
Select distinct p1.playerid,
    stuff(
        (
            Select ','+ convert(varchar, ps2.teamid)
            From plays ps2
            Where ps2.playerid = ps1.playerid ORDER BY ps2.teamid ASC
            For XML PATH ('')
        ),1,1,'') AS teamids 
From player p1 left join plays ps1 on p1.playerid = ps1.playerid
)
select p3.playerid, p3.playername, c1.teamids from player p3 inner join cte1 c1 on 
p3.playerid = c1.playerid where 
(
  select count(teamids) from cte1 c2 where c1.teamids = c2.teamids
) > 1
or
c1.teamids is null
order by teamids DESC

这篇关于请帮我写SQl查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！