mysql - 將別名列添加為新列

我有以下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)

試試這個：

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,
   ((max(case when x.devloc='hvac_main_return' then x.value end)) -     
     (max(case when x.devloc='hvac_main_supply' then x.value end))) AS diff
from sample x where date(timest) = curdate() 
group by timest     
order by timest desc;

如果您想要更詳細的答案，請提供一些範例數據，如我在此處完成的小提琴中一樣。另外，請在此處提供您想要的結果 - 始終在此處提供您包含在任何小提琴中的所有內容（linkrot）。

引用自：https://dba.stackexchange.com/questions/284356