我有两个包含传感器数据的表,我想将两个表合并为一个。
表 1:
Sensor1 | Timestamp
34 2018-22-05 23:23:34
25 2018-22-05 22:23:23
51 2019-22-05 12:34:56
表 2:
Sensor2 | Timestamp
27 2018-22-05 23:37:33
29 2018-22-05 22:36:47
66 2019-22-05 12:34:56
我想要的结果是:
预期表:
Sensor1 | Sensor2 | Timestamp
27 2018-22-05 23:37:33
34 2018-22-05 23:23:34
29 2018-22-05 22:36:47
25 2018-22-05 22:23:23
51 66 2019-22-05 12:34:56
我们不太可能在两个表中具有相同的时间戳,但即使我们这样做,在两个传感器列中都有值也应该不是问题。
我尝试过使用联合、全部联合、外部连接,但我总是以两个时间戳列结束。
有人有想法吗?
谢谢和最好的问候, 罗德里。
您可以尝试此查询。
只需使用UNION ALL
即可合并两个表。
Table1
设置Sensor2
列NULL
Table2
设置Sensor1
列NULL
然后按Timestamp
订购
SELECT * FROM (
select Sensor1 'Sensor1',null 'Sensor2',Ti from T
UNION ALL
select null 'Sensor1', Sensor1 'Sensor2',Ti from T2
) t
order by ti desc
sqlfiddle: http://sqlfiddle.com/#!9/fb22b/9
输出
| Sensor1 | Sensor2 | Ti |
|---------|---------|----------------------|
| (null) | 27 | 2018-05-22T23:37:33Z |
| 34 | (null) | 2018-05-22T23:23:34Z |
| (null) | 29 | 2018-05-22T22:36:47Z |
| 25 | (null) | 2018-05-22T22:23:23Z |
如果要让NULL
为空字符串,则可以使用coalesce
函数。
SELECT coalesce(Sensor1,'') as 'Sensor1',
coalesce(Sensor2,'') as 'Sensor2',
ti
FROM (
select Sensor1 'Sensor1',null 'Sensor2',Ti from T
UNION ALL
select null 'Sensor1', Sensor1 'Sensor2',Ti from T2
) t
order by ti desc
如果表中没有出现重复项,并且您想合并两个表中的结果,我建议聚合:
select max(sensor1) as sensor1, max(sensor2) as sensor2, timestamp
from ((select Sensor1, null as Sensor2, timestamp from t1
) union all
(select null, Sensor2, timestamp
from t2
)
) t
group by timetamp
order by timestamp desc;
select * from (
select sensor1, null as sensor2, timestamp from table1
where timestamp not in (select timestamp from table2)
union all
select null, sensor2, timestamp from table2
where timestamp not in (select timestamp from table1)
union all
select t1.sensor1, t2.sensor2, t1.timestamp from table1 t1
join table2 t2 on t1.timestamp = t2.timestamp
) a
order by timestamp;
根据您使用的 SQL 风格,最简单的方法可能是使用完全外部连接,如下所示;
select
t1.Sensor1,
t2.Sensor2,
coalesce(t1.Timestamp, t2.Timestamp) as Timestamp
from
table1 t1 full outer join table2 t2 on t2.Timestamp = t1.Timestamp