mysql - 如何编写一个返回所有玩家姓名的 SQL 查询……?
问题描述
我想编写一个 SQL 查询,返回他们认识的每个人Players
的所有姓名。PlaysMatches
有3张表:
玩家(ID、姓名)
知道(ID、Player1_ID、Player2_ID)
PlaysMatches(ID、Player1_ID、Player2_ID)
其中 tableKnows
暗示Player1
知道Player2
,tablePlaysMatches
暗示Player1
play match with Player2
。
我的查询的问题是,它返回的球员姓名至少与他们认识的一名球员进行比赛,而不是他们认识的每个人。
我应该在查询中进行哪些更改以使其显示他们认识的每个人?
SELECT DISTINCT P1.name
FROM Players P1, Players P2
WHERE (EXISTS (SELECT *
FROM PlaysMatches PL
WHERE P1.id = PL.Player1_id AND P2.id = PL.Player2_id)
AND EXISTS (SELECT *
FROM Knows K
WHERE P1.id = K.Player1_id AND P2.id = K.Player2_id))
解决方案
我认为您可以通过颠倒当前查询的逻辑来实现我所理解的您想要的。即询问没有记录的球员,不knows
存在匹配的球员。
SELECT p.name
FROM players p
WHERE NOT EXISTS (SELECT *
FROM knows k
WHERE p.id IN (k.player1_id,
k.player2_id)
AND NOT EXISTS (SELECT *
FROM playsmatches pm
WHERE pm.player1_id = k.player1_id
AND pm.player2_id = k.player2_id
OR pm.player1_id = k.player2_id
AND pm.player2_id = k.player1_id));