group by 分组时,如果高效地统计每一组中某一个字段为null的行数

Question

如下要求:
declare @t table(id int null, qt int null)
insert @t values(2, 10);
insert @t values(2, 20);
insert @t values(2, null);
insert @t values(5, 10);
insert @t values(5, null);
insert @t values(5, 30);
insert @t values(6, 20);
insert @t values(8, null);
insert @t values(11, null);
insert @t values(11, 120);
insert @t values(11, null);
insert @t values(12, null);
insert @t values(12, null);
insert @t values(12, null);
insert @t values(13, 30);
insert @t values(13, 40);
insert @t values(13, 50);
select id, sum(qt) qts, count(*) counts, (select count(*) from @t where id = s.id and qt is null) as nulls from @t s group by id
/*
id qts counts nulls
2 30 3 1
5 40 3 1
6 20 1 0
8 NULL 1 1
11 120 3 2
12 NULL 3 3
13 120 3 0
*/
我需要在分组时统计所有销售单明细中某一个字段为null的行数(为什么要在分组时统计就不说了,不是重点),
如果按照上面的方式,使用嵌套查询,那太慢了,
我想问一下,有没有高效的方法,就是求和sum一样,或和count函数一样,不一定要是系统函数就比如select id, sum(qt) qts, count(*) counts, (select xxx(*) where qt is null) as nulls from @t s group by id
或
select id, sum(qt) qts, count(*) counts, xxx(xxx) as nulls from @t s group by id

Answer 1

declare @t table(id int null, qt int null)
insert @t values(2, 10);
insert @t values(2, 20);
insert @t values(2, null);
insert @t values(5, 10);
insert @t values(5, null);
insert @t values(5, 30);
insert @t values(6, 20);
insert @t values(8, null);
insert @t values(11, null);
insert @t values(11, 120);
insert @t values(11, null);
insert @t values(12, null);
insert @t values(12, null);
insert @t values(12, null);
insert @t values(13, 30);
insert @t values(13, 40);
insert @t values(13, 50);select id, sum(qt) qts, count(*) counts
,SUM(CASE WHEN QT IS NULL THEN 1 ELSE 0 END)
from @t s group by id
/*
2 30 3 1
5 40 3 1
6 20 1 0
8 NULL 1 1
11 120 3 2
12 NULL 3 3
13 120 3 0
*/

Answer 2

sum(case when qt is null then 1 else 0 end) as nulls 
或
count(*)-count(qt)   as nulls 

Answer 3

declare @t table(id int null, qt int null)
insert @t values(2, 10);
insert @t values(2, 20);
insert @t values(2, null);
insert @t values(5, 10);
insert @t values(5, null);
insert @t values(5, 30);
insert @t values(6, 20);
insert @t values(8, null);
insert @t values(11, null);
insert @t values(11, 120);
insert @t values(11, null);
insert @t values(12, null);
insert @t values(12, null);
insert @t values(12, null);
insert @t values(13, 30);
insert @t values(13, 40);
insert @t values(13, 50);SELECT id, SUM(qt) qts, COUNT(*) counts, COUNT(*)-COUNT(QT) null_cnt 
FROM @t s
GROUP BY id
/*
id          qts         counts      null_cnt
----------- ----------- ----------- -----------
2           30          3           1
5           40          3           1
6           20          1           0
8           NULL        1           1
11          120         3           2
12          NULL        3           3
13          120         3           0(7 行受影响)
*/

Answer 4

我尽然忘了count是统计非空的行,主要是sql mobile惹得祸啊..没注意
面向 SQL Server Mobile 的 SQL 参考 >  
返回组中的项数。 语法
 
COUNT ( { [ ALL ] expression | * } ) 
 参数
ALL 
对所有值应用聚合函数。默认值为 ALL。expression 
除 uniqueidentifier、image 或 ntext 之外的任何类型的表达式。不允许使用聚合函数和子查询。* 
指定应将所有的行计算在内，以返回表中的行的总数。COUNT(*) 不带参数。COUNT(*) 不需要 expression 参数，因为根据定义，它不会使用有关任何特定列的信息。COUNT(*) 返回指定表的行数，并将重复的行也计算在内。它会单独计算每一行，包括包含 NULL 值的行。返回值不过sql 2005里倒有说明SQL Server 2005 联机丛书  
 
COUNT (Transact-SQL)  发送反馈 
  请参阅    
 全部折叠全部展开 语言筛选器: 全部语言筛选器: 多语言语言筛选器: Visual Basic语言筛选器: C#语言筛选器: C++语言筛选器: J#语言筛选器: JScript 
 
 Visual Basic(Declaration) 
 C#
 C++
 J#
 JScript返回组中的项数。COUNT 与 COUNT_BIG 函数类似。两个函数唯一的差别是它们的返回值。COUNT 始终返回 int 数据类型值。COUNT_BIG 始终返回 bigint 数据类型值。 Transact-SQL 语法约定语法
 
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) 
 备注
COUNT(*) 返回组中的项数。包括 NULL 值和重复项。COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。