I have multiple tables and the column fields and all match data type and column number wise, and all have a time field that also matches in the format of YYYY-MM-DD HH:MM:SS across all.
我有多个表和列字段，都匹配数据类型和列号，并且所有的时间字段都匹配yyyyyyyyy -MM- dd HH:MM:SS的格式。
Data Issue
数据问题
I may have tables where there is a date and time stamp with a matching value in that table's value column, but in the other table it'll not have that same date and time stamp since each table only logs a time when a value fromt he other field is generated.
我可能表哪里有日期和时间戳的匹配值表的列值,但在其他表中没有相同的日期和时间戳的时候因为每个表只记录生成一个fromt他其他字段值。
My Dilemma
我的困境
I need to join all these tables (like 20 or so) so that their time and value fields show with each value from each table having a separate name.
我需要连接所有这些表(大约20个)，以便它们的时间和值字段显示每个表的每个值都有单独的名称。
I also need to show the time for each entry but I need to have just one Time field for all and the other values being Null if it doesn't exist in those tables for that time.
我还需要显示每个条目的时间，但是我只需要一个时间字段来表示所有的，其他的值为Null，如果这个时间字段在那些表中不存在的话。
Example Tables
Table A
表一个
+---------------------+-------+
| Time | Value |
+---------------------+-------+
| 2016-12-11 00:00:15 | 15 |
| 2016-12-11 00:10:10 | 16 |
| 2016-12-11 00:12:00 | 17 |
+---------------------+-------+
Table B
表B
+---------------------+-------+
| Time | Value |
+---------------------+-------+
| 2016-12-11 00:01:15 | 25 |
| 2016-12-11 00:11:10 | 26 |
| 2016-12-11 00:11:00 | 27 |
+---------------------+-------+
TableC
TableC
+---------------------+-------+
| Time | Value |
+---------------------+-------+
| 2016-12-11 00:02:15 | 35 |
| 2016-12-11 00:20:10 | 36 |
| 2016-12-11 00:21:00 | 37 |
+---------------------+-------+
Expected Result
预期的结果
+---------------------+-----------+-----------+-----------+
| Time | Value_tba | Value_tbb | Value_tbc |
+---------------------+-----------+-----------+-----------+
| 2016-12-11 00:00:15 | 15 | Null | Null |
| 2016-12-11 00:10:10 | 16 | Null | Null |
| 2016-12-11 00:12:00 | 17 | Null | Null |
| 2016-12-11 00:01:15 | Null | 25 | Null |
| 2016-12-11 00:11:10 | Null | 26 | Null |
| 2016-12-11 00:11:00 | Null | 27 | Null |
| 2016-12-11 00:02:15 | Null | Null | 35 |
| 2016-12-11 00:20:10 | Null | Null | 36 |
| 2016-12-11 00:21:00 | Null | Null | 37 |
+---------------------+-----------+-----------+-----------+
My Solution with Two Tables
我的解有两个表
If I'm using MYSQL is the only or simplest way to do this with left join and then a UNION or UNION ALL to get this to populate as I'm expecting or is this even possible with an SQL statement to get this result? I have an example below of what I'm using for two tables, but when I need to throw additional tables in the loop, I'm not getting the expected results.
如果我使用MYSQL是唯一或者最简单的方法用左连接然后一个联合或联合来填充它，就像我期望的那样或者用SQL语句来实现这个结果吗?下面有一个例子，说明我在两个表中使用了什么，但是当我需要在循环中抛出额外的表时，我没有得到预期的结果。
Here's what I'm using with two tables which seems to get me what I need but then when I need to add other tables into the mix, this is where I'm getting confused to put all into the same select with additional LEFT JOINs or if I need to populate them separately and then UNION ALL.
这是我使用的两个表,似乎给我我所需要的东西,但当我需要其他表添加到混合,这就是我感到困惑将所有相同的选择额外的左连接或如果我需要单独填充它们,然后联盟所有。
SELECT a.Time, a.Value Value_tba, b.value Value_tbb
FROM TableA a
LEFT JOIN TableB b ON b.Time=a.Time
UNION ALL
SELECT b.Time, a.Value Value_tba, b.value Value_tbb
FROM TableB b
LEFT JOIN TableA a ON a.Time=b.Time
1 个解决方案
#1
2
try the following:
试试以下:
select x.time, a.value as value_tba, b.value as value_tbb, c.value as value_tbc from (select time from a union select time from b union select time from c) x left join a on x.time = a.time left join b on x.time = b.time left join c on x.time = c.time
#1
2
try the following:
试试以下:
select x.time, a.value as value_tba, b.value as value_tbb, c.value as value_tbc from (select time from a union select time from b union select time from c) x left join a on x.time = a.time left join b on x.time = b.time left join c on x.time = c.time