SQL 计数不同和空值被聚合消除

Count distinct and Null value is eliminated by an aggregate

提问by Simon D

I'm using SQL Server 2005. With the query below (simplified from my real query):

我正在使用 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

Is there any way to do a count distinct without getting

有没有办法在没有得到的情况下进行计数

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

“警告：空值被聚合或其他 SET 操作消除。”

Here are the alternatives I can think of:

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

1. Turning ANSI_WARNINGS off

2. Separating into two queries, one with count distinct and a where clause to eliminate nulls, one with the sum:

   select t1.a, t1.countdistinctb, t2.suma from
   (
       select a,count(distinct b) countdistinctb 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
       where a.b is not null
       group by a
   ) t1
   left join
   (
       select a,sum(a) suma 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
   ) t2 on t1.a=t2.a
   

3. Ignore the warning in the client

1. 关闭 ANSI_WARNINGS

2. 分成两个查询，一个是 count distinct 和一个 where 子句来消除空值，一个是总和：

   select t1.a, t1.countdistinctb, t2.suma from
   (
       select a,count(distinct b) countdistinctb 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
       where a.b is not null
       group by a
   ) t1
   left join
   (
       select a,sum(a) suma 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
   ) t2 on t1.a=t2.a
   

3. 忽略客户端中的警告

Is there a better way to do this? I'll probably go down route 2, but don't like the code duplication.

有一个更好的方法吗？我可能会沿着路线 2，但不喜欢代码重复。

采纳答案by Simon D

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

Thanks to Eoin I worked out a way to do this. You can count distinct the values including the nulls and then remove the count due to nulls if there were any using a sum distinct.

感谢 Eoin，我找到了一种方法来做到这一点。您可以计算不同的值，包括空值，然后删除由于空值而产生的计数（如果有的话）。

回答by Eoin Campbell

Anywhere you have a null possibly returned, use

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

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

That will sub out all your Null Values for -1 for the duration of the query and they'll be counted/aggregated as such, then you can just do the reverse in your fine wrapping query...

这将在查询期间将 -1 的所有空值分出，并且它们将被计算/聚合，然后你可以在你的精细包装查询中做相反的事情......

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

回答by Brian Tkatch

This is a late note, but being it was the return on Google, i wanted to mention it.

这是一个迟到的笔记，但它是谷歌的回报，我想提一下。

Changing NULL to another value is a Bad Idea(tm).

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

COUNT() is doing it, not DISTINCT.

COUNT() 正在这样做，而不是 DISTINCT。

Instead, use DISTINCT in an subquery and which returns a number, and aggregate that in the outer query.

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

A simple example of this is:

一个简单的例子是：

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

This allows for COUNT(*)to be used, which does not ignore NULLs (because it counts records, not values).

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

回答by onedaywhen

If you don't like the code duplication then why not use a common table expression? e.g.

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

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