关于#mysql#查询结果，选择其中两项进行处理的问题，如何解决？

SELECT 
g.`name` '业务组',
case q.`type`
WHEN "QUOTA_TYPE_MACHINE_SERVER_NUMBER" then ('虚机数量')
WHEN "QUOTA_TYPE_MACHINE_CPU" then 'CPU'
WHEN "QUOTA_TYPE_MACHINE_MEMORY" then ('内存')
else ('存储')
END '类型',
q.`total` '配额',
q.`used` '已使用',
q.`total`-q.`used` '剩余',
CONCAT(round(q.`used`/q.`total`*100,2), '%') '使用率'
FROM `business_group` g, `business_group_quota` q
WHERE g.`id` = q.`business_group_id`

现在需要将内存和存储的单位转换为G

img

更新，用case这个方法，但是他不进行计算啊

SELECT 
g.`name` '业务组',
CASE q.`type`
WHEN "QUOTA_TYPE_MACHINE_SERVER_NUMBER" then ('虚机数量')
WHEN "QUOTA_TYPE_MACHINE_CPU" then 'CPU'
WHEN "QUOTA_TYPE_MACHINE_MEMORY" then ('内存')
ELSE ('存储')
END '类型',
CASE q.`total`
WHEN q.`type` = 'QUOTA_TYPE_MACHINE_MEMORY' THEN ROUND(q.`total`/1024/1024/1024)
WHEN q.`type` = 'QUOTA_TYPE_MACHINE_STORAGE' THEN ROUND(q.`total`/1024/1024/1024)
ELSE q.`total`
END'配额',
q.`used` '已使用', 
q.`total`-q.`used` '剩余',
CONCAT(ROUND(q.`used`/q.`total`*100,2), '%') '使用率'
FROM `business_group` g, `business_group_quota` q
WHERE g.`id` = q.`business_group_id`

img

那你再写case when then，如果是内存或硬盘，就q.total/1024/1024/1024

转换成G直接把查询的结果除法计算对应的单位就行
q.total/1024 '配额',
q.used/1024 '已使用'
q.total-q.used 剩余
使用率就是 已使用/配额
q.used /q.total '使用率',

case type when '存储' then (CAST(total AS SIGNED integer)/1024/1024/1024) when '内存' then (CAST(total AS SIGNED integer)/1024/1024/1024) else total end '配额'

同样用case when的方法，对内存和存储这两种类型的 配额/1024 ,else其它类型就不除1024

SELECT 
    bg.name "业务组",
    cpu.total "cpu配额(核)",
    cpu.used "cpu已用(核)",
    ROUND(cpu.used/cpu.total*100, 2) "cpu使用率(%)",
    servernu.total "虚机配额(台)",
    servernu.used "虚机已用(台)",
    ROUND(servernu.used/servernu.total*100, 2) "虚机使用率(%)",
    ROUND(mem.total/1024, 0) "内存配额(GB)",
    ROUND(mem.used/1024, 0) "内存已用(GB)",
    ROUND(mem.used/mem.total*100, 2) "内存使用率(%)",
    ROUND(disk.total/1024/1024/1024, 0) "存储配额(GB)",
    ROUND(disk.used/1024/1024/1024, 0) "存储已用(GB)",
    ROUND(disk.used/disk.total*100, 2) "存储使用率(%)"
FROM
    business_group bg
    JOIN (SELECT bgq.used, bgq.total,bgq.business_group_id from business_group_quota bgq where bgq.type = "QUOTA_TYPE_MACHINE_CPU" ) as cpu on cpu.business_group_id = bg.id
    JOIN (SELECT bgq.used, bgq.total,bgq.business_group_id from business_group_quota bgq where bgq.type = "QUOTA_TYPE_MACHINE_MEMORY" ) as mem on mem.business_group_id = bg.id
    JOIN (SELECT bgq.used, bgq.total,bgq.business_group_id from business_group_quota bgq where bgq.type = "QUOTA_TYPE_MACHINE_STORAGE" ) as disk on disk.business_group_id = bg.id
    JOIN (SELECT bgq.used, bgq.total,bgq.business_group_id from business_group_quota bgq where bgq.type = "QUOTA_TYPE_MACHINE_SERVER_NUMBER" ) as servernu on servernu.business_group_id = bg.id

感谢各位老师的指导，同事给了另一个思路要简单一些，发出来仅作参考。