sql - 警告 : Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

Question

当数据为空时，我遇到问题，并且在显示结果时出现警告。如何解决这个问题呢？。如何在表中没有数据的情况下将空数据改为0？

这是我的代码:-

SELECT DISTINCT c.username             AS assigner_officer,
                d.description          AS ticketcategory,
                (SELECT Count(closed)
                 FROM   ticket
                 WHERE  assigned_to = c.user_id
                        AND closed IS NOT NULL
                 GROUP  BY assigned_to)closedcases,
                (SELECT Count(closed)
                 FROM   ticket
                 WHERE  assigned_to = c.user_id
                        AND closed IS NULL
                 GROUP  BY assigned_to)opencases
FROM   ticket a
       JOIN ticketlog b
         ON a.ticketid = b.ticketid
       JOIN access c
         ON a.assigned_to = c.user_id
       JOIN ticket_category d
         ON a.cat_code = d.id
       JOIN lookup_department e
         ON a.department_code = e.code 

结果如下所示:-

 Warnings: ---> 
   W (1): Warning: Null value is eliminated by an aggregate or other SET operation.
          <--- 
 assigner_officer     ticketcategory     closedcases     opencases    
 -------------------  -----------------  --------------  ------------ 
 abdulhafiz           Enquiry            (null)          0            
 affan                Enquiry            12              (null)       
 amirul               Enquiry            1               (null)       
 azrul_fahmi          Enquiry            45              0            
 Azwani               Enquiry            (null)          0            
 chai                 Enquiry            4               (null)       
 dalinawati           Enquiry            1               0            
 Emmy                 Complaints         (null)          0            
 Fadhlia              Enquiry            38              0            
 fairulhalif          Others             1               (null)       
 farikh               Enquiry            (null)          0            
 ismailh              Enquiry            28              0            
 izzahanna            Enquiry            (null)          0            
 Kamsuzilawati        Enquiry            1               (null)     

Answer 1

您通常会使用 COUNT 来通过 UID 进行汇总。因此

COUNT([uid]) 将产生警告:

Warning: Null value is eliminated by an aggregate or other SET operation.

与左连接一起使用时，计数的对象不存在。

在这种情况下使用 COUNT(*) 也会呈现不正确的结果，因为您随后将计算存在的结果(即父项)的总数。

使用COUNT([uid])是一种有效的计数方式，警告只不过是警告。但是，如果您担心，并且希望在这种情况下获得真实的 uid 计数，那么您可以使用:

SUM(CASE WHEN [uid] IS NULL THEN 0 ELSE 1 END) AS [new_count]

这不会给您的查询增加很多开销。(测试mssql 2008)