将T2上的T2与T1连接.如果匹配，则x=T1.x，如果不匹配，则在T2上连接.y=T1.y

Question

我有两个表：一个是Events表，其中包含注册参加活动的团队和用户，另一个是teams表，其中包括所有团队及其玩家(独立于Events(。

我想将Teams加入Teams.user_id=Events.user_id的Events，然后加入Teams.team_id=Events.team_id的Teams中的任何剩余行。

例如，这两个表将产生以下结果

+---------------------------------------+    +-------------------------+
|                 Events                |    |          Teams          |
+----------+---------+---------+--------+    +---------+---------+-----+
| event_id | team_id | user_id |  foo   |    | team_id | user_id | bar |
+----------+---------+---------+--------+    +---------+---------+-----+
|    1     |    1    |    1    |   1    |    |    1    |    1    |  A  |
+----------+---------+---------+--------+    +---------+---------+-----+
|    1     |    1    |    2    |   0    |    |    1    |    2    |  A  |
+----------+---------+---------+--------+    +---------+---------+-----+
|    1     |    2    |   NULL  |  NULL  |    |    2    |    3    |  A  |
+----------+---------+---------+--------+    +---------+---------+-----+
|    1     |    3    |    5    |   0    |    |    2    |    4    |  B  |
+----------+---------+---------+--------+    +---------+---------+-----+
|    3    |    5    |  A  |
+---------+---------+-----+
|    4    |    6    |  A  |
+---------+---------+-----+

+---------------------------------------------+
|               Expected Result               |
+----------+---------+---------+--------+-----+
| event_id | team_id | user_id |  foo   | bar |
+----------+---------+---------+--------+-----+
|    1     |    1    |    1    |   1    |  A  |
+----------+---------+---------+--------+-----+
|    1     |    1    |    2    |   0    |  A  |
+----------+---------+---------+--------+-----+
|    1     |    2    |    3    |  NULL  |  A  |
+----------+---------+---------+--------+-----+
|    1     |    2    |    4    |  NULL  |  B  |
+----------+---------+---------+--------+-----+
|    1     |    3    |    5    |   0    |  A  |
+----------+---------+---------+--------+-----+

我尝试过以下各种查询，但大多数查询的结果是，从Teams中的一个团队到Events中team_id匹配的每个user_id，都会得到整个花名册。

SELECT Events.*, Teams.*
FROM Events
RIGHT JOIN Teams ON CASE
WHEN Teams.user_id = Events.user_id
THEN Teams.user_id = Events.user_id
ELSE Teams.team_id = Events.team_id
END
WHERE Events.event_id = (/* subquery that yields event_id */)
/* or for join */
RIGHT JOIN Teams ON
(Teams.user_id = Events.user_id AND Teams.team_id = Events.team_id)
OR Teams.team_id = Events.team_id
/* also */
RIGHT JOIN Teams ON
(Events.user_id IS NOT NULL AND Teams.user_id = Events.user_id)
OR (Events.user_id IS NULL AND Teams.team_id = Events.team_id)`

编辑：调整表以考虑使用类似LEFT JOIN Teams ON Teams.user_id = Events.user_id OR Teams.team_id = Events.team_id的方法加入的重复用户。

Answer 1

让我知道这是否适用于您的完整数据集。它已经使用了您提供的样本数据，如下面的db fiddle所示：

架构(MySQL v8.0(

CREATE TABLE events (
`event_id` INTEGER,
`team_id` INTEGER,
`user_id` VARCHAR(4),
`foo` VARCHAR(4)
);
INSERT INTO events
(`event_id`, `team_id`, `user_id`, `foo`)
VALUES
('1', '1', '1', '1'),
('1', '2', 'NULL', 'NULL'),
('1', '3', '5', '0');


CREATE TABLE teams (
`team_id` INTEGER,
`user_id` INTEGER,
`bar` VARCHAR(1)
);
INSERT INTO teams
(`team_id`, `user_id`, `bar`)
VALUES
('1', '1', 'A'),
('1', '2', 'A'),
('2', '3', 'A'),
('2', '4', 'B'),
('3', '5', 'A'),
('4', '6', 'A');

---

查询#1

SELECT
e.event_id,
e.team_id,
t.user_id,
CASE
WHEN e.user_id <> t.user_id THEN NULL
ELSE e.foo
END as foo,
t.bar
FROM
events e
INNER JOIN
teams t ON e.user_id = t.user_id OR t.team_id = e.team_id;

user_idfoo

event_id	team_id	bar
1	1	1			A
1	1	2	空	A
1	2	3	空	A
1	2	4	空	B
1	3	5	0	A