22,209
社区成员
发帖
与我相关
我的任务
分享
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
*/
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 行受影响)
*/
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
*/