SQL使用MYSQL连接来自多个表的数据 [英] SQL join data from multiple tables with MYSQL
问题描述
我有多个表，并且所有列字段都与数据类型和列号相匹配，并且所有时间字段也都与YYYY-MM-DD HH:MM:SS格式匹配.
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.
数据问题
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.
我的困境
My Dilemma
我需要连接所有这些表(例如20个左右)，以便它们的时间和值字段与每个表中的每个值都有一个单独的名称一起显示.
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.
我还需要显示每个条目的时间，但是我只需要为所有字段显示一个时间"字段，如果该时间段中的表中不存在其他值，则其他值为"Null".
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.
表A
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 |
+---------------------+-------+
表B
Table 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 |
+---------------------+-----------+-----------+-----------+
我的两个表的解决方案
如果我使用MYSQL是通过左连接执行此操作的唯一或最简单的方法，然后使用UNION或UNION ALL使其按我的期望进行填充，或者甚至可以通过SQL语句来获取此信息结果?下面有一个示例，说明了我用于两个表的内容，但是当我需要在循环中添加其他表时，我没有得到预期的结果.
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.
这是我在两个表中使用的内容，这似乎可以满足我的需要，但是当我需要将其他表添加到组合中时，这让我很困惑，无法将所有表都添加到同一个选择中左联接，或者如果我需要分别填充它们，然后组合全部.
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
推荐答案
尝试以下操作:
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
这篇关于SQL使用MYSQL连接来自多个表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
