MySQL:加入以获取一行中具有相同时间戳的所有数据

Question

我有一个表，tempData，看起来像这样:

+----------+-------+------+---------------------+
| sensorId | temp  | hum  | sampleTime          |
+----------+-------+------+---------------------+
|       11 | 18.80 |   55 | 2015-01-19 22:10:01 |
|       11 | 18.80 |   55 | 2015-01-19 22:15:01 |
|       11 | 18.80 |   55 | 2015-01-19 22:20:01 |
|       21 | 20.00 |   51 | 2015-01-19 22:10:01 |
|       21 | 20.10 |   50 | 2015-01-19 22:15:01 |
|       21 | 20.00 |   51 | 2015-01-19 22:20:01 |
|       31 | 16.10 |   59 | 2015-01-19 22:10:01 |
|       31 | 16.20 |   59 | 2015-01-19 22:15:01 |
|       31 | 16.20 |   59 | 2015-01-19 22:20:01 |
|       41 |  6.30 |   91 | 2015-01-19 22:10:01 |
|       41 |  6.30 |   91 | 2015-01-19 22:15:01 |
|       41 |  6.30 |   91 | 2015-01-19 22:20:01 |
+----------+-------+------+---------------------+

我正在寻找一个查询，该查询会给我一个表，其中两个传感器在同一行(即 11 和 21)上，按 sampleTime 分组:

+---------+--------+---------+--------+---------------------+
| temp_11 | hum_11 | temp_21 | hum_21 | sampleTime          |
+---------+--------+---------+--------+---------------------+
|  18.80  |   55   |  20.00  |   51   | 2015-01-19 22:10:01 |
|  18.80  |   55   |  20.10  |   50   | 2015-01-19 22:15:01 |
|  18.80  |   55   |  20.00  |   51   | 2015-01-19 22:20:01 |
+---------+--------+---------+--------+---------------------+

问候，弗雷德里克

Answer 1

您需要执行 2 个子查询并加入它们:

select [temp_11], [hum_11], [temp_21], [hum_21], sensor11.sampleTime
(
    -- get sensor 11 details
    select temp as [temp_11], hum as [hum_11], sampleTime
        from tempData
        where sensorId = 11
) sensor11
inner join
(
    -- get sensor 21 details
    select temp as [temp_21], hum as [hum_21], sampleTime
        from tempData
        where sensorId = 21
) sensor21 on sensor11.sampleTime = sensor21.sampleTime

或者稍微简洁一些，自连接:

select
    sensor11.temp as [temp_11],
    sensor11.hum as [hum_11],
    sensor21.temp as [temp_21],
    sensor21.hum as [hum_21],
    sensor11.sampleTime
from tempData sensor11
inner join tempData sensor21 on sensor11.sampleTime = sensor21.sampleTime
    and sensor21.sensorId = 21
where sensor11.sensorId = 11

如果你想包括其他传感器，只需继续添加 joins:

select
    sensor11.temp as [temp_11],
    sensor11.hum as [hum_11],
    sensor21.temp as [temp_21],
    sensor21.hum as [hum_21],
    sensor31.temp as [temp_31], -- add
    sensor31.hum as [hum_31], -- add
    sensor11.sampleTime
from tempData sensor11
inner join tempData sensor21 on sensor11.sampleTime = sensor21.sampleTime
    and sensor21.sensorId = 21
inner join tempData sensor31 on sensor11.sampleTime = sensor31.sampleTime -- add
    and sensor31.sensorId = 31 -- add
where sensor11.sensorId = 11