TSQL按机器分组计算生产及非生产环境合规率与统计数量

实现逻辑说明 #

• 以机器为维度分组聚合数据
• 采用条件聚合分别统计生产、非生产两个环境的总条目数、合规条目数
• 计算占比时增加除零防护逻辑，避免某机器无对应环境数据时出现查询报错
• 占比结果默认保留2位小数，符合日常阅读习惯

完整TSQL代码 #

-- 1. 示例表结构&测试数据（实际使用时可删除本段，替换为自己的业务表即可）
IF OBJECT_ID('tempdb..#MachineCompliance') IS NOT NULL DROP TABLE #MachineCompliance
CREATE TABLE #MachineCompliance (
    Machine VARCHAR(10),
    Environment VARCHAR(10),
    Status VARCHAR(20)
)
INSERT INTO #MachineCompliance(Machine, Environment, Status)
VALUES
('GA','PROD','Compliance'),
('GA','PROD','Non Compliance'),
('NY','NonProd','Compliance'),
('NY','NonProd','Non Compliance'),
('CA','PROD','Non Compliance'),
('CA','PROD','Compliance'),
('CA','PROD','Non Compliance'),
('CA','NonProd','Compliance'),
('WA','PROD','Non Compliance'),
('WA','NonProd','Compliance'),
('DN','PROD','Compliance'),
('DN','PROD','Non Compliance')

-- 2. 核心统计查询代码
SELECT 
    Machine,
    -- 生产环境统计字段
    COUNT(CASE WHEN Environment = 'PROD' THEN 1 END) AS Prod总条目数,
    COUNT(CASE WHEN Environment = 'PROD' AND Status = 'Compliance' THEN 1 END) AS Prod合规条目数,
    CASE WHEN COUNT(CASE WHEN Environment = 'PROD' THEN 1 END) = 0 THEN 0.00
         ELSE ROUND(CAST(COUNT(CASE WHEN Environment = 'PROD' AND Status = 'Compliance' THEN 1 END) AS DECIMAL(10,2)) 
                    / COUNT(CASE WHEN Environment = 'PROD' THEN 1 END) * 100, 2) END AS Prod合规占比_百分比,
    -- 非生产环境统计字段
    COUNT(CASE WHEN Environment = 'NonProd' THEN 1 END) AS NonProd总条目数,
    COUNT(CASE WHEN Environment = 'NonProd' AND Status = 'Compliance' THEN 1 END) AS NonProd合规条目数,
    CASE WHEN COUNT(CASE WHEN Environment = 'NonProd' THEN 1 END) = 0 THEN 0.00
         ELSE ROUND(CAST(COUNT(CASE WHEN Environment = 'NonProd' AND Status = 'Compliance' THEN 1 END) AS DECIMAL(10,2)) 
                    / COUNT(CASE WHEN Environment = 'NonProd' THEN 1 END) * 100, 2) END AS NonProd合规占比_百分比
FROM #MachineCompliance
GROUP BY Machine
ORDER BY Machine

结果说明 #

执行上述查询后即可得到每台机器分别对应生产、非生产环境的条目计数和合规占比，完全符合给定的计算公式要求。

内容的提问来源于stack exchange，提问作者Kusu