警告：Null值是通过在数据的Aqua Studio中聚合或其它SET操作消除(Warning: N

我有一个问题，当数据为空，并警告有当结果显示出现。 如何解决这个问题呢？。 如何将空数据更改为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])将产生警告：

警告：Null值由聚合或其它SET操作消除。

同时用左连接，其中所计数的对象不存在被使用。

使用COUNT(*)在这种情况下，同样会导致不正确的结果，因为你会被计数存在的结果（即父母）的总数。

使用COUNT([uid])计数的有效途径，并警告只不过是一个警告的。 但是，如果你担心，你想在这种情况下的UID的真实计数，那么你可以使用：

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

这不是很多费用添加到您的查询。 （测试MSSQL 2008）

Answer 2:

解决这个问题的一个方法就是把警告关闭。

SET ANSI_WARNINGS OFF;
GO

Answer 3:

使用ISNULL(field, 0)它也可以与骨料一起使用：

ISNULL(count(field), 0)

但是，你可能会考虑改变count(field) to count(*)

编辑：

尝试：

closedcases = ISNULL(
   (select count(closed) from ticket       
    where assigned_to = c.user_id and closed is not null       
    group by assigned_to), 0), 

opencases = ISNULL(
    (select count(closed) from ticket 
     where assigned_to = c.user_id and closed is null 
     group by assigned_to), 0),

Answer 4:

你想要把ISNULL的内部COUNT函数，而不是外面：

不好： ISNULL(COUNT(field), 0)

GOOD： COUNT(ISNULL(field, 0))

Answer 5:

我得到这个错误; 我只是把一个WHERE子句这是中使用的场count条款。 它解决了这个问题。 注意：如果空值存在，检查是否其对报告至关重要，因为它在计数排除。

旧的查询：

select city, Count(Emp_ID) as Emp_Count 
from Emp_DB
group by city

新查询：

select city, Count(Emp_ID) as Emp_Count 
from Emp_DB
where Emp_ID is not null
group by city

Answer 6:

如果聚合函数内部存在任何Null值，你将面对这个问题。 而不是下面的代码

 SELECT Count(closed)
  FROM   ticket
  WHERE  assigned_to = c.user_id
  AND closed IS NULL

使用像

SELECT Count(ISNULL(closed, 0))
  FROM   ticket
  WHERE  assigned_to = c.user_id
  AND closed IS NULL

文章来源: Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio