select count(*) from t where b is not null union all select count(*) from t where b is null
select count(*) from t group by (case when b is not null then 1 else 2 end)
--> 测试数据:@tb declare @tb table([A] int,[B] varchar(3)) insert @tb select 1,null union all select 2,'aaa' union all select 3,'bbb' union all select 4,null union all select 5,nullselect count(b),sum(case when [B] is null then 1 else 0 end) from @tb /* ----------- ----------- 2 3 警告: 聚合或其他 SET 操作消除了空值。(1 行受影响) */
--or select count(b),count(1)-count(b) from @tb
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([A] int,[B] varchar(3)) insert [TB] select 1,null union all select 2,'aaa' union all select 3,'bbb' union all select 4,null union all select 5,nullselect count(1) from (select B=case when B is null then null else '' end from TB)g group by B order by 1 /*----------- 2 3(2 行受影响)*/drop table TB
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([A] int,[B] varchar(3)) insert [TB] select 1,null union all select 2,'aaa' union all select 3,'bbb' union all select 4,null union all select 5,nullselect count(1) from TB group by 0/ASCII(B) order by 1 /*----------- 2 3(2 行受影响)*/drop table TB
IF OBJECT_ID('[TB]') IS NOT NULL DROP TABLE [TB] GO CREATE TABLE [TB]([A] INT,[B] VARCHAR(3)) INSERT [TB] SELECT 1,NULL UNION ALL SELECT 2,'AAA' UNION ALL SELECT 3,'BBB' UNION ALL SELECT 4,NULL UNION ALL SELECT 5,NULLSELECT 'NULL数量' 名称,SUM(CASE WHEN B IS NULL THEN 1 ELSE 0 END) 数量 FROM TB UNION ALL SELECT '非NULL数量',SUM(CASE WHEN B IS NULL THEN 0 ELSE 1 END) 数量 FROM TB/* 名称 数量 ---------- ----------- NULL数量 3 非NULL数量 2(2 行受影响) */
select count(*) from TB--统计总的记录数 select count(*) from TB where B isnull--统计B列中属性值为null的记录数 --接下来不用说了吧!
UP, COUNT(1) 是会自动去除NULL的
select count(*) from t where b is not null union all select count(*) from t where b is null
能不能汇总呢。 把不为null的用同一个字符显示。
不会呀,你是不是记错了? --> 测试数据:@tb declare @tb table([A] int,[B] varchar(3)) insert @tb select 1,null union all select 2,'aaa' union all select 3,'bbb' union all select 4,null union all select 5,nullselect count(b),count(1)-count(b) from @tb /* ----------- ----------- 2 3 警告: 聚合或其他 SET 操作消除了空值。(1 行受影响) */
union all
select count(*) from t where b is null
from t
group by (case when b is not null then 1 else 2 end)
declare @tb table([A] int,[B] varchar(3))
insert @tb
select 1,null union all
select 2,'aaa' union all
select 3,'bbb' union all
select 4,null union all
select 5,nullselect count(b),sum(case when [B] is null then 1 else 0 end) from @tb
/*
----------- -----------
2 3
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)
*/
select count(b),count(1)-count(b) from @tb
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([A] int,[B] varchar(3))
insert [TB]
select 1,null union all
select 2,'aaa' union all
select 3,'bbb' union all
select 4,null union all
select 5,nullselect count(1)
from (select B=case when B is null then null else '' end from TB)g
group by B
order by 1
/*-----------
2
3(2 行受影响)*/drop table TB
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([A] int,[B] varchar(3))
insert [TB]
select 1,null union all
select 2,'aaa' union all
select 3,'bbb' union all
select 4,null union all
select 5,nullselect count(1) from TB
group by 0/ASCII(B)
order by 1
/*-----------
2
3(2 行受影响)*/drop table TB
DROP TABLE [TB]
GO
CREATE TABLE [TB]([A] INT,[B] VARCHAR(3))
INSERT [TB]
SELECT 1,NULL UNION ALL
SELECT 2,'AAA' UNION ALL
SELECT 3,'BBB' UNION ALL
SELECT 4,NULL UNION ALL
SELECT 5,NULLSELECT 'NULL数量' 名称,SUM(CASE WHEN B IS NULL THEN 1 ELSE 0 END) 数量 FROM TB
UNION ALL
SELECT '非NULL数量',SUM(CASE WHEN B IS NULL THEN 0 ELSE 1 END) 数量 FROM TB/*
名称 数量
---------- -----------
NULL数量 3
非NULL数量 2(2 行受影响)
*/
select count(*) from TB where B isnull--统计B列中属性值为null的记录数
--接下来不用说了吧!
COUNT(1) 是会自动去除NULL的
union all
select count(*) from t where b is null
把不为null的用同一个字符显示。
--> 测试数据:@tb
declare @tb table([A] int,[B] varchar(3))
insert @tb
select 1,null union all
select 2,'aaa' union all
select 3,'bbb' union all
select 4,null union all
select 5,nullselect count(b),count(1)-count(b) from @tb
/*
----------- -----------
2 3
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)
*/