我有一个表格，每列都有计数（有 20 列），我已经计算0's了过滤后的员工列表在每列中出现的百分比。

以下是我用来测试的简化表定义和数据。我有一张桌子EmployeeNotificationCounts：

CREATE TABLE EmployeeNotificationCounts 
(
    EmployeeId INT NOT NULL,
    EmployeeTypeId INT NOT NULL,
    Notification1 INT NOT NULL,
    Notification2 INT NOT NULL,
    Notification3 INT NOT NULL,
    Notification4 INT NOT NULL
)

INSERT INTO EmployeeNotificationCounts VALUES (1, 1, 15, 0, 0, 5)
INSERT INTO EmployeeNotificationCounts VALUES (2, 2, 1, 3, 0, 5)
INSERT INTO EmployeeNotificationCounts VALUES (3, 1, 15, 0, 0, 5)
INSERT INTO EmployeeNotificationCounts VALUES (4, 2, 3, 6, 0, 15)
INSERT INTO EmployeeNotificationCounts VALUES (5, 1, 15, 0, 0, 5)
INSERT INTO EmployeeNotificationCounts VALUES (6, 1, 15, 0, 0, 5)
INSERT INTO EmployeeNotificationCounts VALUES (7, 2, 18, 0, 0, 25)
INSERT INTO EmployeeNotificationCounts VALUES (8, 1, 15, 0, 0, 5)
INSERT INTO EmployeeNotificationCounts VALUES (9, 2, 0, 14, 0, 35)
INSERT INTO EmployeeNotificationCounts VALUES (10, 1, 15, 0, 0, 5)

对于所有类型 = 2 的员工，我想在每个通知中以 0 的百分比获得如下结果：

Notification1  Notificatio2  Notification3  Notification4
---------------------------------------------------------
10              70            100             0          

我有一个查询可以完成这项工作

SELECT 
    ((SELECT COUNT(*) FROM EmployeeNotificationCounts 
      WHERE Notification1 = 0 AND EmployeeTypeId = 2) * 100 / COUNT(*)) AS IncompleteChart0Percentage,
    ((SELECT COUNT(*) FROM EmployeeNotificationCounts 
      WHERE Notification2 = 0 AND EmployeeTypeId = 2) * 100 / COUNT(*)) AS IntComm0Percentage,
    ((SELECT COUNT(*) FROM EmployeeNotificationCounts 
      WHERE Notification3 = 0 AND EmployeeTypeId = 2) * 100 / COUNT(*)) AS PTComm0Percentage,
    ((SELECT COUNT(*) FROM EmployeeNotificationCounts 
      WHERE Notification4 = 0 AND EmployeeTypeId = 2) * 100 / COUNT(*)) AS PendingBilling0Percentage
FROM 
    EmployeeNotificationCounts ENC
WHERE
    ENC.EmployeeTypeId = 2

我有这个查询可以根据需要获得结果。但是，这可以针对更大的数据库进行优化吗？在生产中，该表可能有数千条记录。我还没有在生产中运行它，只是想确定百分比计算语句是否可以用不同的方式编写。我在这里先向您的帮助表示感谢。

回答

Select Notification1 = avg(case when Notification1 = 0 then 100.0 else 0 end) 
      ,Notification2 = avg(case when Notification2 = 0 then 100.0 else 0 end) 
      ,Notification3 = avg(case when Notification3 = 0 then 100.0 else 0 end) 
      ,Notification4 = avg(case when Notification4 = 0 then 100.0 else 0 end) 
 From EmployeeNotificationCounts
 Where ENC.EmployeeTypeId = 2

Notification1   Notification2   Notification3   Notification4
10.000000       70.000000       100.000000      0.000000