sql - 如何对2个查询的输出执行算术运算

Question

以下是 2 个查询:

SELECT COUNT(BUG.BG_BUG_ID) AS BUG_ID_SIT_CNT 
FROM BUG 
WHERE BUG.BG_USER_02 = 'SIT'

SELECT COUNT(BUG.BG_BUG_ID) AS BUG_ID_UAT_CNT 
FROM BUG 
WHERE BUG.BG_USER_02 = 'UAT'

如何编写查询以使用上述查询的结果并执行以下操作:

缺陷去除效率:BUG_ID_SIT_CNT/(BUG_ID_SIT_CNT + BUG_ID_UAT_CNT)

Answer 1

SELECT BUG_ID_SIT_CNT * 1. / BUG_ID_ALL
FROM (
    SELECT
        COUNT(CASE WHEN BG_USER_02 = 'SIT' THEN BG_BUG_ID END) AS BUG_ID_SIT_CNT,
        COUNT(BG_BUG_ID) AS BUG_ID_ALL
    FROM BUG
    WHERE BG_USER_02 IN ('SIT', 'UAT')
) t