如何简单的判断表中符合条件的某列是全为null 还是部分为null

[Kingofcode]

如题：如何简单的判断表中符合条件的某列是全为null 还是部分为null

例如表 table1(a,b,c) 如何简单判断当a=1的时候的所有数据 c是全为空还是部分为空

[發糞塗牆]

count(c)，count单列的时候会忽略null值， 如果count(c)=0就是全部null

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-26 15:57:06
-- Version:
--      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
--	Jun 28 2012 08:36:30 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([a] int,[c] nvarchar(2))
insert [huang]
select 1,null union all
select 1,null union all
select 2,null union all
select 2,'a'
--------------生成数据--------------------------

select COUNT(c)[全部null]
from [huang]
WHERE a=1

SELECT COUNT(c)[部分null]
FROM huang 
WHERE a=2
----------------结果----------------------------
/* 
全部null
-----------
0

(1 row(s) affected)

部分null
-----------
1
*/

[Kingofcode]

这样判断全部为null很好解决 为0 那如果全都不为空或者部分为空呢？

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-26 15:57:06
-- Version:
--      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
--	Jun 28 2012 08:36:30 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([a] int,[c] nvarchar(2))
insert [huang]
select 1,null union all
select 1,null union all
select 2,null union all
select 2,'a'union all
select 3,'a'union all
select 3,'a'
--------------生成数据--------------------------

SELECT a,CASE WHEN COUNT(c)=0 THEN N'全部为null' WHEN COUNT(c)=COUNT(1) THEN N'全部不为null' ELSE N'部分为null' END [null的数量]
FROM huang 
GROUP BY a
----------------结果----------------------------
/* 
a           null的数量
----------- --------
1           全部为null
2           部分为null
3           全部不为null
*/

[Kingofcode]

重要的是判断状态，具体数据不重要

[發糞塗牆]

回复引用一下

[Kingofcode]

太厉害了 回复神速，赶紧把分全给你

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-06-26 16:02:03
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] int,[col2] sql_variant)
insert [tb]
select 1,null union all
select 2,null union all
select 3,null
--------------开始查询--------------------------
select isnull(col1,0),isnull(col2,0) from [tb]
----------------结果----------------------------
/* ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           0
2           0
3           0

*/

[Kingofcode]

也谢谢 fredrickhu 你们太厉害了 又快又正确