计算不同值并通过聚合消除 Null 值

Question

我正在使用 SQL Server 2005。使用下面的查询（从我的实际查询简化）：

select a,count(distinct b),sum(a) from 
(select 1 a,1 b union all
select 2,2 union all
select 2,null union all
select 3,3 union all
select 3,null union all
select 3,null) a
group by a

有没有办法在不出现

“警告：聚合或其他 SET 操作消除空值”的情况下进行非重复计数。

以下是我能想到的替代方案：

1. 关闭 ANSI_WARNINGS

2. 分成两个查询，一个具有不同的计数，一个 where 子句用于消除空值，一个具有总和：

   从中选择 t1.a、t1.countdistinctb、t2.suma 
     （ 
         选择 a,count(distinct b) countdistinctb from  
         （ 
             选择 1 a,1 b 并集所有 
             选择 2,2 并集所有 
             选择2，空并集所有 
             选择 3,3 并集所有 
             选择3，空并集所有 
             选择3，空 
         ） A 
         其中 ab 不为空 
         按一个分组 
     ) t1 
     左连接 
     （ 
         选择 a,sum(a) suma 从中  
         （ 
             选择 1 a,1 b 并集所有 
             选择 2,2 并集所有 
             选择2，空联合所有 
             选择 3,3 并集所有 
             选择3，空并集所有 
             选择3，空 
         ） A 
         按一个分组 
     ) t2 于 t1.a=t2.a 
     

3. 忽略客户端中的警告

Is有更好的方法吗？ 我可能会选择路线 2，但不喜欢代码重复。

Answer 1

select a,count(distinct isnull(b,-1))-sum(distinct case when b is null then 1 else 0 end),sum(a) from 
    (select 1 a,1 b union all
    select 2,2 union all
    select 2,null union all
    select 3,3 union all
    select 3,null union all
    select 3,null) a
    group by a

感谢 Eoin，我找到了一种方法来做到这一点。 您可以对包括空值在内的不同值进行计数，然后使用不同值之和删除由于空值而导致的计数（如果有）。

Answer 2

在任何可能返回 null 的地方，使用

CASE WHEN Column IS NULL THEN -1 ELSE Column END AS Column

That 会在查询期间将所有 Null 值替换为 -1，并且它们将被如此计数/聚合，然后您可以在精细包装查询中执行相反的操作。 ..

SELECT  
    CASE WHEN t1.a = -1 THEN NULL ELSE t1.a END as a
    , t1.countdistinctb
    , t2.suma

Answer 3

这是一个迟到的说明，但由于这是谷歌的回报，我想提一下。

将 NULL 更改为另一个值是一个坏主意。

COUNT() 正在执行此操作，而不是 DISTINCT。

相反，在子查询中使用 DISTINCT 并返回一个数字，然后在外部查询中聚合该数字。

一个简单的例子是：

WITH A(A) AS (SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT 1)
SELECT COUNT(*) FROM (SELECT DISTINCT A FROM A) B;

这允许使用 COUNT(*)，它不会忽略 NULL（因为它计算记录，而不是值）。

Answer 4

如果您不喜欢代码重复，那么为什么不使用公用表表达式呢？ 例如

WITH x(a, b) AS 
        (
                select 1 a,1 b union all
                select 2,2 union all
                select 2,null union all
                select 3,3 union all
                select 3,null union all
                select 3,null
        ) 
select t1.a, t1.countdistinctb, t2.suma from
(
        select a,count(distinct b) countdistinctb from 
        x a
        where a.b is not null
        group by a
) t1
left join
(
        select a,sum(a) suma from 
        x a
        group by a
) t2 on t1.a=t2.a