查询3个表中所有数据的最快方法
查询3个表中所有数据的最快方法
提问于 2017-06-12 13:31:34
假设我有三张这样的桌子：
Table 1
|Id|Date |Data1|
|--|---- |-----|
|1 |24/05|Some1|Table 2
|Id|Date |Data2|
|--|---- |-----|
|1 |24/05|Some2|
|1 |12/06|Some2|Table 3
|Id|Date |Data3|
|--|---- |-----|
|1 |24/05|Some3|
|1 |14/06|Some3|我怎样才能得到这样的结果：
|Id|Date |Data1|Data2|Data3|
|--|-----|-----|-----|-----|
|1 |24/05|Some1|Some2|Some3|
|1 |12/06|NULL |Some2|NULL |
|1 |14/06|NULL |NULL |Some3|使用MS。
我想出的是
SELECT Id, Date, Data1, Data2, Data3 FROM Table1
LEFT JOIN Table2 ON Table1.Id = Table2.Id AND Table1.Date = Table2.Date
LEFT JOIN Table3 ON Table1.Id = Table3.Id AND Table1.Date = Table3.Date
UNION
SELECT Id, Date, Data1, Data2, Data3 FROM Table2
LEFT JOIN Table1 ON Table2.Id = Table1.Id AND Table2.Date = Table1.Date
LEFT JOIN Table3 ON Table2.Id = Table3.Id AND Table2.Date = Table3.Date
UNION
SELECT Id, Date, Data1, Data2, Data3 FROM Table3
LEFT JOIN Table1 ON Table3.Id = Table1.Id AND Table3.Date = Table3.Date
LEFT JOIN Table2 ON Table3.Id = Table2.Id AND Table3.Date = Table2.Date有更好的方法来查询数据吗？
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-06-12 13:40:37
select
id = coalesce(t1.id,t2.id,t3.id)
, [date] = coalesce(t1.[date],t2.[date],t3.[date])
, t1.Data1
, t2.Data2
, t3.Data3
from Table1 t1
full join Table2 t2
on t1.id = t2.id
and t1.[date]=t2.[date]
full join Table3 t3
on (t1.id = t3.id and t1.[date]=t3.[date])
or (t2.id = t3.id and t2.[date]=t3.[date])rextester演示：http://rextester.com/ATCJ12705
返回：
+----+-------+-------+-------+-------+
| id | date | Data1 | Data2 | Data3 |
+----+-------+-------+-------+-------+
| 1 | 24/05 | Some1 | Some2 | Some3 |
| 1 | 12/06 | NULL | Some2 | NULL |
| 1 | 14/06 | NULL | NULL | Some3 |
+----+-------+-------+-------+-------+Stack Overflow用户
发布于 2017-06-12 13:39:38
一种快速的方法是对临时表中的所有日期和it进行排序，然后将数据放在表中。
Select T.id,T.date, T1.Data1,T2.Data2,T3.Data3
FROM
(
select id,date from Table1
UNION
select id,date from Table2
UNION
select id,date from Table3
) T
LEFT JOIN Table1 T1 on T.id=T1.id and T.date=T1.date
LEFT JOIN Table2 T2 on T.id=T2.id and T.date=T2.date
LEFT JOIN Table3 T3 on T.id=T3.id and T.date=T3.date
ORDER BY T.id,T.date唯一表的JOINs=数
Stack Overflow用户
发布于 2017-06-12 14:24:50
假设每个表对于每个id和日期只有1行，那么在UNION中放置null值并选择MAX值可以达到相同的效果：
create table #t1 (id int, [date] nvarchar(5), data1 nvarchar(10))
create table #t2 (id int, [date] nvarchar(5), data2 nvarchar(10))
create table #t3 (id int, [date] nvarchar(5), data3 nvarchar(10))
insert into #t1 values (1, '24/05', 'Some1')
insert into #t2 values (1, '24/05', 'Some2'), (1, '12/06', 'Some2')
insert into #t3 values (1, '24/05', 'Some3'), (1, '14/06', 'Some3')
select a.id, a.[date], max(a.data1) as [Data1], max(a.data2) as [Data2], max(a.data3) as [Data3]
from
(
select id, [date], [data1] as [Data1], Null as [Data2], null as [Data3] from #t1
union
select id, [date], null as [Data1], [data2] as [Data2], null as [Data3] from #t2
union
select id, [date], null as [Data1], Null as [Data2], [Data3] as [Data3] from #t3
) a
group by a.id, a.[date]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44500749
复制相关文章
相似问题
腾讯云开发者
