在子查询 JOIN 中引用外部查询

2022-01-23 00:00:00 join subquery mysql

我在这里呼吁 MySQL 勇士.我的问题是我需要在子查询的左外部联接中使用对外部查询表的引用.我知道这是不可能的，但我不知道如何用外部查询中的典型连接替换子查询过滤.

I'm calling on for MySQL warriors here. My problem is that I need to use a reference to an outer query's table within a left outer join in an subquery. I know this is not possible, but I have no idea on how to replace the subquery filtering with the typical joins in the outer query.

我将举一个典型的例子来说明我的问题.

I'll take a typical example to illustrate my issue.

这是奥运会，我有几支球队(比如乒乓球队)，其中有 1 到 N 名球员.当所有球员都与对方球队的所有球员交手时，两支球队之间的比赛就结束了.简而言之，我有 3 张桌子:

It's the Olympic games, I have several teams (say table tennis teams) in which there are 1 to N players. A match between two teams is finished when all of its players have played against all of the opposing team's players. In brief, I have 3 tables : 

Team (id, country); 
Player (id, team_id, name) 
Game (id, playerA_id, playerB_id).

我想搜索所有没有或不是每场比赛都与对方球队的所有球员(显然不在我的球队中)发生争议的球员.这是我的查询:

I want to search all players that have disputed none or not every game against all of the opposing team's players (and who are not in my team obviously). Here is my query :

SELECT t.*, p.*
FROM player p
INNER JOIN team t ON t.id = p.team_id AND t.id != My_Team_ID
WHERE EXISTS (
    -- If an opposing team's player has played no game => game.id is NULL
    -- If he hasn't played against all of my teammates => there will be at least one row where game.id is NULL
    SELECT *
    FROM player
    INNER JOIN team ON team.id = player.team_id AND team.id = My_Team_ID
    LEFT OUTER JOIN game ON player.id IN (game.playerA_id, game.playerB_id) AND p.id IN (game.playerA_id, game.playerB_id)
    WHERE game.id IS NULL
)

出于实用目的，我放了一个转储:http://pastebin.com/HW3L5ukz我试图将 LEFT OUTER JOIN 的第二个条件放在子查询的 WHERE 中，但它没有返回正确的结果.

I've put a dump for practical purposes : http://pastebin.com/HW3L5ukz I tried to put the 2nd condition of the LEFT OUTER JOIN in the WHERE in the subquery but it doesn't return the proper results.

关于如何实现这一目标的任何想法?提前致谢

Any idea on how to achieve this ? Thanks in advance

推荐答案

SELECT p1.*, p2.*
FROM player p1
JOIN team t1
  ON t1.id = p1.team_id AND t1.id = My_Team_ID
LEFT JOIN player p2
  ON p2.id != p1.id
JOIN team t2
  ON t2.id = p2.team_id AND t2.id != My_Team_ID
LEFT JOIN game g1
  ON (g1.playerA_id = p1.id OR g1.playerB_id = p1.id)
  AND (g1.playerA_id = p2.id OR g1.playerB_id = p2.id)
WHERE g1.id IS NULL

如果我为 My_Team_ID 使用 1，我会得到以下结果，其中显示了剩余的匹配项:

If I use 1 for My_Team_ID, I get the following results, which shows the remaining matches:

id  team_id name            id  team_id     name
1   1       Laurent Dupuis  6   2           Alec Russell
2   1       Stéphane Leroy  6   2           Alec Russell
3   1       Julien le Guen  4   2           Mark Johnsson
3   1       Julien le Guen  6   2           Alec Russell

相关文章