22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:@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,null
select count(b),count(1)-count(b) from @tb
/*
----------- -----------
2 3
警告: 聚合或其他 SET 操作消除了空值。
(1 行受影响)
*/
select count(*) from TB--统计总的记录数
select count(*) from TB where B isnull--统计B列中属性值为null的记录数
--接下来不用说了吧!
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,NULL
SELECT '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 行受影响)
*/
--> 测试数据:[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,null
select count(1) from TB
group by 0/ASCII(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,null
select 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
--or
select count(b),count(1)-count(b) from @tb
--> 测试数据:@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,null
select count(b),sum(case when [B] is null then 1 else 0 end) from @tb
/*
----------- -----------
2 3
警告: 聚合或其他 SET 操作消除了空值。
(1 行受影响)
*/