如何将4个表连接成一个表？
如何将4个表连接成一个表？
提问于 2015-06-10 14:51:10
表-1
name closedid
rere 4
trtr 5
ewew 6和表2
name openedid
rere 6
trtr 7
ytyt 8
uyuy 5和
table-3
name assign
rere 6
ytyt 8
uyuy 5
rtyy 9和表4
name unassign
rere 6
trtr 7
errt 5
hdtg 9我想要像这样的最终输出：
name closedid opened assign unassign
rere 4 6 6 6
trtr 5 7 null 7
ytyt null 8 8 null
uyuy null 5 5 null
ewew 6 null null null
rtyy null null 9 null
errt null null null 5
hdtg null null null 9回答 5
Stack Overflow用户
发布于 2015-06-10 15:08:32
我想，这就是你要找的：
SELECT coalesce(t1.NAME, t2.NAME, t3.NAME, t4.NAME) NAME
,t1.closedid
,t2.openedid
,t3.assign
,t4.unassign
FROM [table-1] t1
FULL OUTER JOIN [table-2] t2 ON t1.NAME = t2.NAME
FULL OUTER JOIN [table-3] t3 ON isnull(t1.NAME, t2.NAME) = t3.NAME
FULL OUTER JOIN [table-4] t4 ON coalesce(t1.NAME, t2.NAME, t3.NAME) = t4.NAMEStack Overflow用户
发布于 2015-06-10 15:02:12
我会抓取所有不同的名称，然后与该列表进行左连接，这将使理解发生了什么变得更容易。
;WITH cte(name)
AS (
SELECT name FROM table1
UNION
SELECT name FROM table2
UNION
SELECT name FROM table3
UNION
SELECT name FROM table4
)
SELECT
cte.name,
table1.closedid,
table2.openid,
table3.assign,
table4.unassign
FROM
cte
LEFT JOIN table1 ON cte.name = table1.name
LEFT JOIN table2 on cte.name = table2.name
LEFT JOIN table3 on cte.name = table3.name
LEFT JOIN table4 on cte.name = table4.nameSQLFiddle
Stack Overflow用户
发布于 2015-06-13 00:52:09
- 使用
UNION获取您独特的名称列表 - 使用
LEFT JOIN检索您的data
示例：
SELECT list.name, t1.assigned, t2.unsassigned, t3.closed, t4.open
FROM
(select name from table1
union select name from table2
union select name from table3
union select name from table4
) list
LEFT JOIN table1 t1 ON (list.name = t1.name)
LEFT JOIN table2 t2 ON (list.name = t2.name)
LEFT JOIN table3 t3 ON (list.name = t3.name)
LEFT JOIN table4 t4 ON (list.name = t4.name)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30749054
复制相关文章
相似问题
腾讯云开发者
