我正在运行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 |
+-----------------+--------+这就是我正在尝试做的事情,如果in Status列的值是0,那么打印Available,如果值在16到20之间,打印值是In-used,我如何动态更改返回值的结果?
最终结果应该如下所示
+-----------------+----------------+
| Non-HT Computes | Status |
+-----------------+----------------+
| 20 | Available |
| 5 | In-Used |
+-----------------+----------------+我尝试过CASE语句,但不确定如何将其与别名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 |
+-----------+发布于 2020-05-27 05:14:19
这可能会起到作用。如果你想在非零情况下进行限制,你可以使用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;发布于 2020-05-27 05:15:09
第一个选项是使用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_usedhttps://stackoverflow.com/questions/62031286
复制相似问题