mysql - 三重连接sql

Question

我想在使用查询时从我的 sql 数据库中获取一些特定数据。我的第一个表包含我的应用程序的所有团队。 (表格1)第二个表包含另一个表之间的关系:该表包含以下字段:

• 唯一标识
• TeamUniqueId(表1的uniqueId)

我想要内部连接相反的数据:

Select * From table_1
Full outer join table_2
on table_1.UniqueId = table_2.table_1UnqiueId
Where table_1.UniqueId IS NULL
OR table_2.UniqueId IS NULL

所以这给出了正确的数据。

---

但现在我想将此数据与 table_3 进行比较，我想要所有未与第 3 个表链接的数据

表_3 1.唯一ID 2. TeamUniqueId(第一个表的uniqueId)

放在图片里

Answer 1

我想我可能会使用更暴力的方法:

select uniqueid
from ((select uniqueid, 1 as t1, 0 as t2, 0 as t3
       from table_1
      ) union all
      (select uniqueid, 0 as t1, 1 as t2, 0 as t3
       from table_2
      ) union all
      (select uniqueid, 0 as t1, 0 as t2, 1 as t3
       from table_3
      ) 
     ) t
group by uniqueid
having (sum(t1) = 1 or  -- in table1
        sum(t2) = 1     -- or table2
       ) and
       count(*) = 1;  -- in only one table

在某些数据库中，您还可以使用集合函数。像这样:

((select unique1
  from table_1
  union
  select unique1
  from table_2
 ) minus  -- or except
 (select unique1
  from table_1 t1
  intersect
  select unique1
  table_2 t2
 )
) minus  -- or except
select unique1
from table_3;