主页 / dba / 问题 / 284356
Accepted
mikem
Asked: 2021-01-31 15:37:04 +0800 CST

mysql - 将别名列添加为新列

  • 772

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

2 个回答

  1. Best Answer

    尝试这个：

    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）。

    • 2

  2. 通过将当前查询包装为子查询：

    SELECT timest,
       outside,
       hvac_main_return,
       hvac_main_supply,
       hvac_main_supply - hvac_main_return AS hvac_main_net
FROM
  (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) AS y
ORDER BY timest DESC;

    在子查询中也是如此GROUP BY，因为它需要对 产生影响max，但是在ORDER BY子查询之外，因为它将在子查询中被忽略（子查询的顺序并不重要，对于结果，只有外部查询）。

    • 1

相关问题