警告:Aqua Data Studio 中的聚合或其他 SET 操作会消除空值 [英] Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

问题描述

当数据为空时出现问题，并且在显示结果时出现警告.如何解决这个问题呢?.表中没有数据时如何将空数据更改为0?.

I have a problem when data is null and the warning has appear when the result is display. How to solve this problem?. How to change the null data to 0 when no data in the table?.

这是我的代码:-

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)     

推荐答案

您通常会使用 COUNT 对 UID 进行汇总.因此

You would mostly be using COUNT to summarize over a UID. Therefore

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

警告:空值会被聚合或其他 SET 操作消除.

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

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

whilst being used with a left join, where the counted object does not exist.

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

Using COUNT(*) in this case would also render incorrect results, as you would then be counting the total number of results (ie parents) that exist.

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

Using COUNT([uid]) IS a valid way of counting, and the warning is nothing more than a warning. However if you are concerned, and you want to get a true count of uids in this case then you could use:

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

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

This would not add a lot of overheads to your query. (tested mssql 2008)

这篇关于警告:Aqua Data Studio 中的聚合或其他 SET 操作会消除空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！