具有多个表和关系的复杂SQL查询
在这个查询中，我必须列出一对具有playerID和playerName的球员，他们为3支球队踢球，一个球员为3支球队踢球，另一对球员必须为3支球队踢球。不能少，不能多。如果两名球员目前没有为任何球队效力，他们也应该被包括在内。查询应该返回(playerID1，playername1，playerID2，playerName2)，没有重复，比如如果玩家1的信息在玩家2之前，那么玩家2的信息不应该在玩家1之前。
例如，如果球员A效力于洋基队和红袜队，而球员B效力于洋基队、红袜队和道奇队，我就不应该得到他们。他们都必须为洋基和红袜队效力，而不是为其他人效力。现在，如果球员为任何同一支球队踢球，这个查询就会找到答案。
Tables:
player(playerID: integer, playerName: string)
team(teamID: integer, teamName: string, sport: string)
plays(playerID: integer, teamID: integer)
Example data:
PLAYER
playerID playerName
1 Rondo
2 Allen
3 Pierce
4 Garnett
5 Perkins
TEAM
teamID teamName sport
1 Celtics Basketball
2 Lakers Basketball
3 Patriots Football
4 Red Sox Baseball
5 Bulls Basketball
PLAYS
playerID TeamID
1 1
1 2
1 3
2 1
2 3
3 1
3 3所以我应该得到这个答案-
2, Allen, 3, Pierce
4, Garnett, 5, Perkins。
2，阿伦，3皮尔斯是一名狙击手，因为两人都只为凯尔特人和爱国者4，5岁的加内特，帕金斯是一个答案，因为两名球员都没有为球队比赛，这应该是输出。
现在我得到的查询是
SELECT p1.PLAYERID,
f1.PLAYERNAME,
p2.PLAYERID,
f2.PLAYERNAME
FROM PLAYER f1,
PLAYER f2,
PLAYS p1
FULL OUTER JOIN PLAYS p2
ON p1.PLAYERID < p2.PLAYERID
AND p1.TEAMID = p2.TEAMID
GROUP BY p1.PLAYERID,
f1.PLAYERID,
p2.PLAYERID,
f2.PLAYERID
HAVING Count(p1.PLAYERID) = Count(*)
AND Count(p2.PLAYERID) = Count(*)
AND p1.PLAYERID = f1.PLAYERID
AND p2.PLAYERID = f2.PLAYERID; 我不是百分之百确定，但我认为这会找到为同一支球队踢球的球员，但我想找出只为上面解释的所有相同球队踢球的球员。
在这之后，我被困在了如何处理它上。任何关于如何处理这个问题的提示。耽误您时间，实在对不起。
回答 16
Stack Overflow用户
发布于 2013-07-31 16:31:04
我相信这个查询会做你想要的：
SELECT array_agg(players), player_teams
FROM (
SELECT DISTINCT t1.t1player AS players, t1.player_teams
FROM (
SELECT
p.playerid AS t1id,
concat(p.playerid,':', p.playername, ' ') AS t1player,
array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
FROM player p
LEFT JOIN plays pl ON p.playerid = pl.playerid
GROUP BY p.playerid, p.playername
) t1
INNER JOIN (
SELECT
p.playerid AS t2id,
array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
FROM player p
LEFT JOIN plays pl ON p.playerid = pl.playerid
GROUP BY p.playerid, p.playername
) t2 ON t1.player_teams=t2.player_teams AND t1.t1id <> t2.t2id
) innerQuery
GROUP BY player_teamsResult:
PLAYERS PLAYER_TEAMS
2:Allen,3:Pierce 1,3
4:Garnett,5:Perkins它对plays中的每个球员使用array_agg，以匹配具有完全相同团队配置的球员。例如，我在团队中包含了一个列，但只要它没有从group by子句中删除，就可以在不影响结果的情况下删除它。
使用Postgesql9.2.4测试的SQL Fiddle example.
编辑:修复了重复行的错误。
Stack Overflow用户
发布于 2013-11-10 06:47:06
看起来OP可能不会再感兴趣了，但是以防其他人发现它有用，这是用纯SQL实现的查询(至少对我来说是这样；)
SELECT M.p1, pr1.playername, M.p2, pr2.playername FROM player pr1
INNER JOIN player pr2 INNER JOIN
(
SELECT plays1.player p1, plays2.player p2, plays1.team t1 FROM plays plays1
INNER JOIN plays plays2
ON (plays1.player < plays2.player AND plays1.team = plays2.team)
GROUP BY plays1.player, plays2.player HAVING COUNT(*) =
((SELECT COUNT(*) FROM plays plays3 WHERE plays3.player = plays1.player) +
(SELECT COUNT(*) FROM plays plays4 WHERE plays4.player = plays2.player)) /2
) M ON pr1.playerID = M.p1 AND pr2.playerID = M.p2
UNION ALL
SELECT M.pid, M.pname, N.pid2, N.pname2 FROM
(
(SELECT p.playerID pid, p.playerName pname, pl.team FROM player p
LEFT JOIN plays pl ON p.playerId = pl.player WHERE pl.team IS NULL) M
INNER JOIN
(SELECT p.playerID pid2, p.playerName pname2, pl.team FROM player p
LEFT JOIN plays pl ON p.playerId = pl.player WHERE pl.team IS NULL) N
ON (pid < pid2)
)Stack Overflow用户
发布于 2014-01-16 15:11:04
这没什么大不了的，这里有个解决方案
with gigo as(select a.playerid as playerid,count(b.teamname) as nteams from player a
full outer join plays c on a.playerid=c.playerid full outer join team b
on b.teamid=c.teamid group by a.playerid)
select array_agg(a.*),g.nteams from player a inner join gigo g on a.playerid=g.playerid
group by g.nteams having count(a.*)>1 order by g.nteams deschttps://stackoverflow.com/questions/17963411
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号