我家里有一堆温度传感器。我每分钟轮询一次传感器，并将数据存储在 mysql 数据库中，如下所示：
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| property | varchar(50) | YES | MUL | NULL | |
| devloc | varchar(50) | YES | MUL | NULL | |
| sensortype | varchar(50) | YES | MUL | NULL | |
| timest | datetime | YES | MUL | NULL | |
| value | decimal(8,4) | NO | MUL | NULL | |
+------------+--------------+------+-----+---------+-------+
对于任何给定的轮询周期，我都会对 17 个传感器进行采样，每个传感器都有一个唯一的“devloc”。特定样本的时间戳对于所有 17 个条目都是相同的。给定的样本运行可以报告如下：
mysql> select * from sample where date(timest) = curdate() and
property='myhouse' order by timest desc limit 17;
+-----------+---------------------+------------+---------------------+---------+
| property | devloc | sensortype | timest | value |
+-----------+---------------------+------------+---------------------+---------+
| myhouse | garage_ext1 | temp | 2021-01-29 00:19:01 | 34.4750 |
| myhouse | hvac_attic_supply | temp | 2021-01-29 00:19:01 | 73.5125 |
| myhouse | hvac_attic_return | temp | 2021-01-29 00:19:01 | 70.9250 |
| myhouse | hvac_main_supply | temp | 2021-01-29 00:19:01 | 79.7000 |
| myhouse | hvac_main_return | temp | 2021-01-29 00:19:01 | 66.6500 |
| myhouse | attic_side | temp | 2021-01-29 00:19:01 | 69.8000 |
| myhouse | attic_main | temp | 2021-01-29 00:19:01 | 69.2375 |
| myhouse | upstairs_right_bed | temp | 2021-01-29 00:19:01 | 76.5500 |
| myhouse | upstairs_middle_bed | temp | 2021-01-29 00:19:01 | 70.3625 |
| myhouse | upstairs_guest_bed | temp | 2021-01-29 00:19:01 | 72.6125 |
| myhouse | garage_main | temp | 2021-01-29 00:19:01 | 54.9500 |
| myhouse | basement | temp | 2021-01-29 00:19:01 | 63.6125 |
| myhouse | study | temp | 2021-01-29 00:19:01 | 71.1500 |
| myhouse | master_bed | temp | 2021-01-29 00:19:01 | 69.4625 |
| myhouse | kitchen | temp | 2021-01-29 00:19:01 | 69.3500 |
| myhouse | den | temp | 2021-01-29 00:19:01 | 69.6875 |
| myhouse | outside | temp | 2021-01-29 00:17:52 | 24.3500 |
+-----------+---------------------+------------+---------------------+---------+
我想报告给定日期（或日期范围）的 3 个特定 devloc 值。我想在每个采样间隔内在同一行上查看外部、hvac_main_return 和 hvac_main_supply。如下所示：
+---------------------+---------+------------------+------------------+----------+
| timest | outside | hvac_main_return | hvac_main_supply | property |
+---------------------+---------+------------------+------------------+----------+
| 2021-01-29 00:19:01 | 24.3500 | 66.6500 | 79.7000 | myhouse |
| 2021-01-29 00:20:01 | 24.1000 | 71.5200 | 87.6125 | myhouse |
... etc ... etc...
+---------------------+---------+------------------+------------------+----------+
我可以编写简单的查询，但这个超出了我的能力范围。我的数据库/SQL 知识有限。
任何帮助都将不胜感激。谢谢
答案1
我想我找到了答案在（https://stackoverflow.com/questions/8283541/merging-multiple-rows-into-one-row-and-multiple-columns-on-mysql）。
经过一些修改，我得到了这个：
select x.timest,
max(case when x.devloc='outside' then x.value end) as outside,
max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,
max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply
from sample x where date(timest) = curdate()
group by timest
order by timest desc;
它给我这样的输出：
+---------------------+---------+------------------+------------------+
| timest | outside | hvac_main_return | hvac_main_supply |
+---------------------+---------+------------------+------------------+
| 2021-01-28 23:59:54 | 24.8000 | 67.4375 | 82.9625 |
| 2021-01-28 23:58:45 | 24.9125 | 67.1000 | 80.8250 |
| 2021-01-28 23:57:42 | 24.9125 | 66.0875 | 78.2375 |
| 2021-01-28 23:56:33 | 24.9125 | 64.9625 | 74.8625 |
| 2021-01-28 23:55:32 | 25.0250 | 62.3750 | 73.0625 |
| 2021-01-28 23:54:17 | 25.0250 | 62.8250 | 74.7500 |
+---------------------+---------+------------------+------------------+
我可以调整其中日期（timest）= curdate（）给我一个具体的日期或日期范围。
有人知道我如何将 hvac_main_return 和 hvac_main_supply 之间的差异添加为另一列吗？