用于从3个表中获取信息的Mysql查询

Question

我有下面3个表，我需要从这些表中获取信息。nagios_performance_data nagios_large_performance_data client_circuit_mapping

下面是这些表中的示例数据，

nagios_performance_data
 circuit_name               | record_date         | latency | packetloss |
+----------------------------+---------------------+---------+------------+
| Device 1                  | 2016-11-28 04:40:00 |   72.54 |       0.00

nagios_large_performance_data
+----------------------------+---------------------+---------+------------+
| circuit_name               | record_date         | latency | packetloss |
+----------------------------+---------------------+---------+------------+
| Device 1                   | 0000-00-00 00:00:00 |   83.00 |       0.00 |
+----------------------------+---------------------+---------+------------+

client_circuit_mapping
+-------------------------------+---------------------------------------+
| circuit_name                  | groupname                             |
+-------------------------------+---------------------------------------+
| Device1                       |Group1                                 |
+-------------------------------+---------------------------------------+

现在我需要查询这三个表以获得以下输出，

client_circuit_mapping.groupname、nagios_performance_data.circuit_name、nagios_performance_data.record_date、nagios_performance_data.packetloss、nagios_performance_data.latency、nagios_large_performance_data.latency

是否有人可以帮助我查询以获得此输出

Answer 1

只需使用circuit_name join这三个表

select 
    c.groupname,
    a.circuit_name,
    a.record_date,
    a.packetloss,
    a.latency,
    b.latency
from nagios_performance_data a
join nagios_large_performance_data b on a.circuit_name = b.circuit_name
join client_circuit_mapping on a.circuit_name = c.circuit_name

Answer 2

您需要基于circuit_name列来连接表：

SELECT A.*, B.group_name, C.latency large_latency FROM nagios_performance_data A
INNER JOIN client_circuit_mapping B ON B.circuit_name = A.circuit_name
INNER JOIN nagios_large_performance_data C ON C.circuit_name = A.circuit_name

请注意，我对nagios_large_performance_data.latency列使用了别名。