我有以下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 |
+---------------------+---------+------------------+------------------+
有谁知道我如何将 hvac_main_return 和 hvac_main_supply 之间的差异添加为另一列？
编辑：按要求提供更多信息：
mysql> show create table sample\G
*************************** 1. row ***************************
Table: sample
Create Table: CREATE TABLE `sample` (
`property` varchar(50) DEFAULT NULL,
`devloc` varchar(50) DEFAULT NULL,
`sensortype` varchar(50) DEFAULT NULL,
`timest` datetime DEFAULT NULL,
`value` decimal(8,4) NOT NULL,
KEY `property` (`property`),
KEY `devloc` (`devloc`),
KEY `sensortype` (`sensortype`),
KEY `timest` (`timest`),
KEY `value` (`value`),
KEY `timest_2` (`timest`,`devloc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)