mysql根据条件改变查询结果

9jyewag0 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(356)

我正在运行mariadb版本的mysql，我的查询结果如下

select count(*) as 'Non-HT Computes', vcpus_used as 'Status'
from compute_nodes
WHERE deleted_at is NULL
  AND hypervisor_hostname LIKE '%sriov%'
  AND vcpus < '21'
GROUP BY vcpus_used

+-----------------+--------+
| Non-HT Computes | Status |
+-----------------+--------+
|              20 |      0 |
|               5 |     16 |
+-----------------+--------+

这就是我想做的如果在 Status 列值为 0 然后打印 Available 如果值介于 16 至 20 打印值 In-used ，如何动态更改返回值的结果？
最终结果应该是

+-----------------+----------------+
| Non-HT Computes |      Status    |
+-----------------+----------------+
|              20 |     Available  |
|               5 |     In-Used    |
+-----------------+----------------+

我试过了 CASE 语句，但不确定如何将其与alias组合在一起 Status 列，因为 Status 是伪列。
编辑：
我走了这么远，但还没找到印刷的方法 Non-HT Computes 表格：(

select CASE WHEN Status = 0 THEN 'available' ELSE 'In-used' END AS Status from (select count(*) as 'Non-HT Computes',vcpus_used as 'Status' from compute_nodes WHERE deleted_at is NULL AND hypervisor_hostname LIKE '%sriov%' AND vcpus < '21' GROUP BY vcpus_used) as a
+-----------+
| Status    |
+-----------+
| available |
| In-used   |
+-----------+

sql mysql

来源：https://stackoverflow.com/questions/62031286/mysql-change-query-result-based-on-condition

2条答案

按热度按时间

roqulrg31#

这也许能起作用。如果你想限制后非零的情况下，你可以使用 having 声明。

SELECT count(*) AS 'Non-HT Computes',
       CASE
           WHEN vcpus_used BETWEEN 16 AND 20 THEN "In-used"
           WHEN vcpus_used = 0 THEN "Available"
           ELSE "Non-Zero"
           END AS Status
FROM compute_nodes
WHERE deleted_at IS NULL
  AND hypervisor_hostname LIKE '%sriov%'
  AND vcpus < '21'
GROUP BY vcpus_used;

赞(0）回复(0）举报 2021-07-26

wz3gfoph2#

第一种选择是使用 CASE :

SELECT count(*) AS 'Non-HT Computes',
       CASE
           WHEN vcpus_used = 0 THEN 'Available'
           WHEN vcpus_used >= 16 AND vcpus_used <= 20 THEN 'In-use'
       END  AS 'Status'
FROM compute_nodes
WHERE deleted_at IS NULL
  AND hypervisor_hostname LIKE '%sriov%'
  AND vcpus < '21'
GROUP BY vcpus_used

第二个选项是创建一个具有状态值（0、16、20）的表，并针对该表进行连接。

CREATE TABLE status_text (
    id INT PRIMARY KEY,
    descr VARCHAR(32)
);

INSERT INTO status_text VALUES (0,'Available');
INSERT INTO status_text VALUES (16,'In-use');
INSERT INTO status_text VALUES (20,'In-use');

SELECT count(*) AS 'Non-HT Computes',descr AS status
FROM compute_nodes
JOIN status_text ON (id=vcpus_used) 
WHERE deleted_at IS NULL
  AND hypervisor_hostname LIKE '%sriov%'
  AND vcpus < '21'
GROUP BY vcpus_used

赞(0）回复(0）举报 2021-07-26

我来回答

mysql根据条件改变查询结果

2条答案

相关问题

热门标签

最新问答