Question

sql 语句,如何将数个相同表中的数据合并到一张表中

我有这样三张或者更多的表，格式为
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再合并吗???

Answer 1

--测试表及数据
CREATE TABLE table1
(
val VARCHAR(20),
tm DATETIME
)
INSERT INTO table1 VALUES('a1','2012-7-31 01:00:00')
INSERT INTO table1 VALUES('a3','2012-7-31 03:00:00')
INSERT INTO table1 VALUES('a4','2012-7-31 04:00:00')
INSERT INTO table1 VALUES('a5','2012-7-31 05:00:00')
CREATE TABLE table2
(
val VARCHAR(20),
tm DATETIME
)
INSERT INTO table2 VALUES('b1','2012-7-31 01:00:00')
INSERT INTO table2 VALUES('b2','2012-7-31 02:00:00')
INSERT INTO table2 VALUES('b4','2012-7-31 04:00:00')
INSERT INTO table2 VALUES('b5','2012-7-31 05:00:00')
CREATE TABLE table3
(
val VARCHAR(20),
tm DATETIME
)
INSERT INTO table3 VALUES('c1','2012-7-31 01:00:00')
INSERT INTO table3 VALUES('c2','2012-7-31 02:00:00')
INSERT INTO table3 VALUES('c3','2012-7-31 03:00:00')
INSERT INTO table3 VALUES('c5','2012-7-31 05:00:00')
SELECT t1.val AS value1,t2.val AS value2,t3.val AS value3,temp.tm FROM (SELECT tm FROM table1
UNION
SELECT tm FROM table2
UNION
SELECT tm FROM table3) AS temp
LEFT JOIN table1 AS t1 ON temp.tm=t1.tm
LEFT JOIN table2 AS t2 ON temp.tm=t2.tm
LEFT JOIN table3 AS t3 ON temp.tm=t3.tm

查询结果
a1 b1 c1 2012-07-31 01:00:00.000
NULL b2 c2 2012-07-31 02:00:00.000
a3 NULL c3 2012-07-31 03:00:00.000
a4 b4 NULL 2012-07-31 04:00:00.000
a5 b5 c5 2012-07-31 05:00:00.000

Answer 2

字段名vale改为val、time改为tm
select nvl(t1.val,'null') value1, nvl(t2.val,'null') value2, nvl(t3.val,'null') value3, tt.tm from
(select distinct tm from (select tm from table1 union select tm from table2 union select tm from table3))
tt,table1 t1,table2 t2,table3 t3 where tt.tm=t1.tm(+) and tt.tm = t2.tm(+) and tt.tm = t3.tm(+);

VALUE1 VALUE2 VALUE3 TM
-------------------- -------------------- -------------------- -----------
a1 b1 c1 2012-7-31 1
null b2 c2 2012-7-31 2
a3 null c3 2012-7-31 3
a4 b4 null 2012-7-31 4
a5 b5 c5 2012-7-31 5