请问有如下的数据如何进行查询

[Rubi]

有如下的数据，

A B C D
42000001 1000 1
42000001 2001 1.5
42000001 3001 1.7 3.1
42000002 1000 1 3
42000002 2001 2 6
42000002 2003 3 3
42000002 2005 7 0


如果对A列来说，比如42000002，它一共有4行数据，对应的C、D字段中都有数据内容，那么我就认为这个数据项是完整的。但是对于42000001来说，虽然他B字段3001对应的C、D字段都有数据，但是其他B字段对应的都缺一个数据，我认为该42000002数据不完整，在数据库中存在着这样大量的数据，请问我用SQl该如何查询出来像42000002这样完整的数据出来呢？

[开着拖拉机泡妞]

看我博客，怎么查找有为空的行。

[Mr_Nice]

select distinct A from TB where c is not null and D is not null

这样？

[SqlServer2008]

select * from tab where A not in
(
select distinct A from tab where c is null or d is null
)

[开着拖拉机泡妞]

我的那篇博客名字叫《case when的一个用法》

[Mr_Nice]

[Quote=引用 2 楼 的回复:]

SQL code
select distinct A from TB where c is not null and D is not null


这样？
[/Quote]

看差了。。。

select distinct A.A
from TB A
where not exists(select 1 from TB where A.A = A and (c is null or d is null))

[昵称被占用了]

select diatinct a
from tab a
where not exists (
  select 1 from tab
  where a = a.a
  and (c is null or d is null)
  )

[黄_瓜]

--> 测试数据：#tt
if object_id('tempdb.dbo.#tt') is not null drop table #tt
go 
create table #tt([A] int,[B] int,[C] numeric(2,1),[D] numeric(2,1))
insert #tt
select 42000001,1000,1,null union all
select 42000001,2001,1.5,null union all
select 42000001,3001,1.7,3.1 union all
select 42000002,1000,1,3 union all
select 42000002,2001,2,6 union all
select 42000002,2003,3,3 union all
select 42000002,2005,7,0
--------------开始查询--------------------------

select * from #tt a where not exists(select 1 from #tt b where a.[A]=b.[A] and (b.[C] is  null or b.[D] is  null))
----------------结果----------------------------
/* 
A           B           C                                       D
----------- ----------- --------------------------------------- ---------------------------------------
42000002    1000        1.0                                     3.0
42000002    2001        2.0                                     6.0
42000002    2003        3.0                                     3.0
42000002    2005        7.0                                     0.0

(4 行受影响)


*/

[开着拖拉机泡妞]

--创建测试数据：
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
col1 varchar(5),
col2 varchar(5),
col3 varchar(5),
col4 varchar(5),
col5 varchar(5),
col6 varchar(5),
col7 varchar(5),
col8 varchar(5)
)

insert tbl
select '1',5,'2',5,'3','4','5','6' union all
select '1','2','3',null,null,null,null,'4' union all
select '2','2',null,'3','4','5','6','7' union all
select '3',2,3,'2','3',3,'4',2
--静态实现方法
--"+"想加统计各个“case when else”的结果来达到目的：
select *, 
case when col1 is not null then 1 else 0 end+
case when col2 is not null then 1 else 0 end+
case when col3 is not null then 1 else 0 end+
case when col4 is not null then 1 else 0 end+
case when col5 is not null then 1 else 0 end+
case when col6 is not null then 1 else 0 end+
case when col7 is not null then 1 else 0 end+
case when col8 is  null then 1 else 0 end as notnull
from tbl
--动态实现方法

declare @str varchar(8000)
set @str=''
select @str= @str+'+ case when ' + name +' is null then 1 else 0 end'+char(13)+char(10)
--char(13)+char(10)实现换行
from syscolumns where id = OBJECT_ID('tbl')
--print @str
set @str='select col1,col2,col3,col4,col5,col6,col7,col8 from(select *,'+@str+' as notnull from tbl)a where notnull=0'
--print @str
exec(@str)
/*
1	5	2	5	3	4	5	6
3	2	3	2	3	3	4	2

*/

[Rubi]

谢谢各位大侠