我有这样三张或者更多的表,格式为
table1
value time
a1 2012-7-31 01:00:00
a3 2012-7-31 03:00:00
a4 2012-7-31 04:00:00
a5 2012-7-31 05:00:00
table2
value time
b1 2012-7-31 01:00:00
b2 2012-7-31 02:00:00
b4 2012-7-31 04:00:00
b5 2012-7-31 05:00:00
table3
value time
c1 2012-7-31 01:00:00
c2 2012-7-31 02:00:00
c3 2012-7-31 03:00:00
c5 2012-7-31 05:00:00
我要怎么写sql语句能得到如下结果?
value1 value2 value3 time
a1 b1 c1 2012-7-31 01:00:00
NULL b2 c2 2012-7-31 02:00:00
a3 NULL c3 2012-7-31 03:00:00
a4 b4 NULL 2012-7-31 04:00:00
a5 b5 c5 2012-7-31 05:00:00
是用full join再合并吗???
谢谢回答,不过有点问题,结果里面会有两条time为 2012-7-31 02:00:00 的值:
NULL b2 NULL
NULL NULL c2
这两条没有合并起来,还有就是如果有3个以上这样的表,该怎么写啊,谢谢!
那检查一下time的数据类型是什么呢?
如果两条,说明table2里的和table3里的这个值是不相等的
如果有更多表的话,from。。。 这里,就是加full join 就可以了
但是select 里的 time列值选取,要注意一下
不过也简单,就是套 isnull ,比如多一个table4
select a.[value] as value1,b.[value] as value2, c.[value] as value3,d.[value] as value4,
isnull(isnull(isnull(a.time,b.time),c.time),d.time) as time
from table1 a full join table2 b on a.time = b.time
full join table3 c on a.time = c.time
full join table4 d on a.time = d.time