警告: 在 Aqua Data Studio 中，聚合操作或其他 SET 操作将消除空值

我有一个问题时，数据为空，并出现警告时，结果显示。 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)
343078 次浏览
小开

使用 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),
小开
最佳答案

您通常使用 COUNT来汇总 UID

COUNT([uid])将发出警告:

警告: 聚合或其他 SET 操作将消除空值。

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

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

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

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

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

小开

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

SET ANSI_WARNINGS OFF;
GO
小开

你想把 ISNULL放在 COUNT函数的内部，而不是外部:

Not GOOD: ISNULL(COUNT(field), 0)

GOOD: COUNT(ISNULL(field, 0))

小开

如果聚合函数中存在任何 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
小开

我得到了这个错误; 我只是为在 count子句中使用的字段放置了一个 WHERE子句。问题解决了。注意: 如果空值存在，请检查它是否对报告至关重要，因为它被排除在计数之外。

老问题:

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

提交答案