判断多行列值为空的情况

Question

有个表有三个字段，第一个字段可能有重复值，可能在两行以上，第三列有可能为空，也可能不为空。现需要实现下面的功能，根据第一列的值进行分组查询，如果第三列全为空返回“全部空值”，如果不全为空（有空值，也有非空值）返回“不全为空值”，如果全不为空则返回“没有空值”
比如下面的数据
AA  1   ABA
AA  1   AB比如上面的数据就要返回下面的结果
AA  '没有空值'

Answer 1

--TravyLee生成测试数据:
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
id int,
name varchar(10),
value varchar(10)
)
insert test
select 1,'lee','test01' union all
select 1,'lee',null union all
select 2,'lee','test01' union all
select 2,'lee','test02' union all
select 2,'lee','test03' union all
select 2,'lee','test04' union all
select 3,'lee',null union all
select 4,'lee','test01' union all
select 4,'lee','test01' union all
select 5,'lee','test01'
go
with t
as(
select
id,
COUNT(1) as total,
SUM(case when value is null then 0 else 1 end) as [isnull]
from 
test
group by
id
)
select
id,
case when total=[isnull] then '不全为空值' 
when total>[isnull] then '存在'+LTRIM(total-[isnull])+'个空值'
when [isnull]=0 then '全部为空' end as 是否为空
from
t
/*
id 是否为空
1 存在1个空值
2 不全为空值
3 存在1个空值
4 不全为空值
5 不全为空值
*/

Answer 2

select col1,case when num=[count] then '没有空值' 
                 when num=0 then '全部为空'
                 else '不全为空值' end
from (select col1,num=sum(case when col3 is null then 0 else 1 end),
        [count]=count(*) from tb group by col1) t

Answer 3

--TravyLee生成测试数据:
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
id int,
name varchar(10),
value varchar(10)
)
insert test
select 1,'lee','test01' union all
select 1,'lee',null union all
select 2,'lee','test01' union all
select 2,'lee','test02' union all
select 2,'lee','test03' union all
select 2,'lee','test04' union all
select 3,'lee',null union all
select 4,'lee','test01' union all
select 4,'lee','test01' union all
select 5,'lee','test01'
go
with t
as(
select
id,
COUNT(1) as total,
SUM(case when value is null then 0 else 1 end) as [isnull]
from 
test
group by
id
)
select
id,
case when total=[isnull] then '不存在空值' 
when total>[isnull] then '存在'+LTRIM(total-[isnull])+'个空值'
when [isnull]=0 then '全部为空' end as 是否为空
from
t
/*
id 是否为空
1 存在1个空值
2 不存在空值
3 存在1个空值
4 不存在空值
5 不存在空值
*/
修改了一下

Answer 4

with t
as
这是SQL2005以上的？在SQL2000下好象没见过

Answer 5

select
    id,
    case when total=[isnull] then '不存在空值' 
        when total>[isnull] then '存在'+LTRIM(total-[isnull])+'个空值'
        when [isnull]=0 then '全部为空' end as 是否为空
from
    (select
    id,
    COUNT(1) as total,
    SUM(case when value is null then 0 else 1 end) as [isnull]
from 
    test
group by
    id)t--这样就行了

Answer 6

我知道我已经改成那样了。我只是多嘴问下想知道那个with是干吗用的

Answer 7

create table test
(
    id int,
    name varchar(10),
    value varchar(10)
)
insert test
select 1,'lee','test01' union all
select 1,'lee',null union all
select 2,'lee','test01' union all
select 2,'lee','test02' union all
select 2,'lee','test03' union all
select 2,'lee','test04' union all
select 3,'lee',null union all
select 4,'lee','test01' union all
select 4,'lee','test01' union all
select 5,'lee','test01'
go
select id,
case when id2=0 then '全部空值' 
     when id1=id2 then '没有空值' 
     else '不全为空值' end as [状态] 
from 
(select id,COUNT(*) as id1,COUNT(value) as id2 from test group by id) a

Answer 8

--TravyLee生成测试数据:
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
    id int,
    name varchar(10),
    value varchar(10)
)
insert test
select 1,'lee','test01' union all
select 1,'lee',null union all
select 2,'lee','test01' union all
select 2,'lee','test02' union all
select 2,'lee','test03' union all
select 2,'lee','test04' union all
select 3,'lee',null union all
select 4,'lee','test01' union all
select 4,'lee','test01' union all
select 5,'lee','test01'
goDECLARE @t table( id int, [sum] int , [path] xml );;with c1(id, [sum], [path]) as
(
SELECT 
ID,
COUNT(ID) 'sum',
CAST( (SELECT value from test where id = t.id FOR XML PATH('')) AS XML ) 'path'
FROM test t
GROUP BY id
)
INSERT INTO @t --统计包含null的记录
SELECT ID, [sum], [Path] FROM c1;with c2 as --从xml段拆分，null忽略
(
SELECT t.id, b.v
FROM @t t
CROSS APPLY
(SELECT v = t1.x.value('.', 'varchar(10)') FROM t.[Path].nodes('//value') AS t1(x) ) b
)
,c3 as
(
SELECT id, 
COUNT(v) Remaining  --统计不包含null的记录
FROM c2
GROUP BY id
)
SELECT t.id,
t.[sum],
c3.Remaining,
CASE 
WHEN t.[sum] = Remaining THEN '没有空值'
WHEN Remaining IS NULL THEN '全为空值'
WHEN t.[sum] > Remaining THEN '不全为空值'
END 'result'
FROM @t t
LEFT JOIN c3 ON t.id = c3.idid          sum         Remaining   result
----------- ----------- ----------- ----------
1           2           1           不全为空值
2           4           4           没有空值
3           1           NULL        全为空值
4           2           2           没有空值
5           1           1           没有空值(5 行受影响)借用Lee的数据，嘿嘿……

Answer 9

http://topic.csdn.net/u/20121031/18/f7328c3c-2f05-4f11-a6cc-3c3143f900ff.html同时帮忙看看这个问题