gpt4 book ai didi

mySql - (反向)从多个表和多个关系进行左连接

转载 作者:行者123 更新时间:2023-11-30 00:34:18 28 4
gpt4 key购买 nike

有一个表 ServErog(服务),它被重新引导到 4 个表 ServA、ServB、ServC、ServD(它们是不同的非统一服务),其中包含 servtype(服务类型)和 type_id(来自其中一个的数字 ID) 4服务表)

结构(简化):

ServErog

mysql> select * from ServErog
+----+-------+----------+------+
| idSE | servtype | type_id |
+----+-------+----------+------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 4 | 1 |
| 4 | 3 | 1 |
| 5 | 1 | 2 |
+----+-------+----------+-------+

服务A

mysql> select * from ServA
+----+-------+----------+------+
| idSA | service_code | type |
|+----+-------+----------+------+
| 1 | codice bla | 1 |
| 2 | codice ecc | 1 |
| 3 | bla bla | 1 |
+----+-------+----------+------+

服务B

mysql> select * from ServB
+----+-------+----------+------+
| idSB | service_code | type |
+----+-------+----------+------+
| 1 | codice bla | 2 |
| 2 | codice ecc | 2 |
| 3 | bla bla | 2 |
+----+-------+----------+------+

ServC

mysql> select * from ServC
+----+-------+----------+------+
| idSC | service_code | type |
+----+-------+----------+------+
| 1 | codice bla | 3 |
| 2 | codice ecc | 3 |
| 3 | bla bla | 3 |
+----+-------+----------+------+

服务D

mysql> select * from ServD
+----+-------+----------+------+
| idSA | service_code | type |
+----+-------+----------+------+
| 1 | codice bla | 4 |
| 2 | codice ecc | 4 |
| 3 | bla bla | 4 |
+----+-------+----------+------+

左连接

Select
ServErog.idSE,
ServErog.servtype,
ServErog.typeid,
ServA.idSA,
ServA.type,
ServB.idSB,
ServB.type,
Serv.idSA,
Serv.type,
ServD.idSA,
ServD.type
From
ServErog
Left Join
ServA On ServErog.servtype = ServA.type And ServA.idSA = ServErog.typeid
Left Join
ServB On ServErog.servtype = ServB.type And ServB.idSB = ServErog.typeid
Left Join
ServC On ServErog.servtype = ServC.type And ServC.idSC = ServErog.typeid
Left Join
ServD On ServErog.servtype = ServD.type And ServD.idSD = ServErog.typeid
Order By
ServErog.idSE

+----+-------+----------+------+------+------+---------+
| idSE | servtype | type_id | idSA | idSB | idSC | idSD|
+----+-------+----------+------+------+------+---------+
| 1 | 1 | 1 | 1 | null | null | null |
| 2 | 2 | 1 | null | 1 | null | null |
| 3 | 4 | 1 | null | null | null | 1 |
| 4 | 3 | 1 | null | null | 1 | null |
| 5 | 1 | 2 | 2 | null | null | null |
+----+-------+----------+------+------+

这将返回所有用 ServErog 重新引导的记录。完美!

现在我需要显示 ServErog 中不存在的 ServA、ServB、ServC、ServD 的所有记录。就像先例 Join 的逆操作一样。我尝试过右连接,idSE 为空但没有结果

这就是我要寻找的示例:

+----+-------+----------+------+
| idSA | idSB | idSC | idSD|
+----+-------+----------+------+
| 3 | null | null | null |
| null | 2 | null | null |
| null | 3 | null | null |
| null | null |2 | null |
|null | null | 3 | null |
| null | null |null | 2 |
| null | null | null | 3 |
+----+-------+----------+------+

最佳答案

你可以做这样的事情(因为我不知道在这种情况下如何在没有 UNION 的情况下获得扁平列表,也许有人会发现一些更优雅的东西)。

select 
max(i.idSA) as idSA,
max(i.idSB) as idSB,
max(i.idSC) as idSC,
max(i.idSD) as idSD
FROM

(select se.idSE as idSA, null as idSB, null as idSC, null as idSD
FROM ServA s
left join ServErog se On se.servtype = s.type And s.idSA = se.typeid

UNION

select null , se.idSE, null, null
FROM ServB s
left join ServErog se On se.servtype = s.type And s.idSA = se.typeid

UNION

select null , null, se.idSE, null
FROM ServC s
left join ServErog se On se.servtype = s.type And s.idSA = se.typeid

UNION

select null , null, null, se.idSE
FROM ServD s
join ServErog se On se.servtype = s.type And s.idSA = se.typeid) i

关于mySql - (反向)从多个表和多个关系进行左连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22297352/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com