有一表,里面有DWDM,DM两个字段,我想查询这样的结果:
比如DWDM为5的,它的所有数据中的DM列的值都为'-',我把这个DWDM取出来。如数据:
DWDM DM
1 5
1 -
2 -
2 -
3 -
3 s
3 -
4 -那我的结果是DWDM值为:2和4
谢谢!
比如DWDM为5的,它的所有数据中的DM列的值都为'-',我把这个DWDM取出来。如数据:
DWDM DM
1 5
1 -
2 -
2 -
3 -
3 s
3 -
4 -那我的结果是DWDM值为:2和4
谢谢!
GROUP BY DM HAVING COUNT(DM)=
(SELECT COUNT(DM) FROM TB GROUP BY DM WHERE DM=N'-')
select * from TB a where not exists(select 1 from TB where a.DWDM=DWDM and DM<>'-')
where not exists(select 1 from table where a.DWDM=DWDM and DM<>'-')
create table #T(DWDM int,DM varchar(50))
insert into #T
select 1 ,'5' union all
select 1 ,'-' union all
select 2 ,'-' union all
select 2 ,'-' union all
select 3 ,'-' union all
select 3 ,'s' union all
select 3 ,'-' union all
select 4 ,'-' select distinct DWDM from #T a where not exists(select 1 from #T where a.DWDM=DWDM and DM<>'-')----------------------------------------
2
4
INSERT @TB
SELECT 1 , '5' UNION ALL
SELECT 1 , '-' UNION ALL
SELECT 2 , '-' UNION ALL
SELECT 2 , '-' UNION ALL
SELECT 3 , '-' UNION ALL
SELECT 3 , 's' UNION ALL
SELECT 3 , '-' UNION ALL
SELECT 4 , '-' SELECT DWDM FROM @TB T
GROUP BY DWDM
HAVING COUNT(DM)=(SELECT COUNT(DM) FROM @TB WHERE DM='-' AND DWDM=T.DWDM GROUP BY DWDM)
(所影响的行数为 8 行)DWDM
-----------
2
4(所影响的行数为 2 行)
--SELECT COUNT(DM) FROM @TB WHERE DM='-' GROUP BY DWDM
drop table a;create table a(dwdm int, dm varchar(4));
insert into a(dwdm, dm)
select
1,'5' union all select
1,'-' union all select
2,'-' union all select
2,'-' union all select
3,'-' union all select
3,'s' union all select
3,'-' union all select
4,'-';select dwdm
from a
group by dwdm
having count(dwdm)=sum(case dm when '-' then 1 else 0 end);
insert into #tmp
select 1, '5' union all
select 1, '-' union all
select 2, '-' union all
select 2, '-' union all
select 3, '-' union all
select 3, 's' union all
select 3, '-' union all
select 4, '-'
select distinct * from #tmp t where not exists(select 1 from #tmp where DWDM=t.DWDM and DM<>'-')
create table tb20091029_1 (DWDM int,DM varchar(10))
insert into tb20091029_1
select '1','5'
union all select '1','-'
union all select '2','-'
union all select '2','-'
union all select '3','-'
union all select '3','s'
union all select '3','-'
union all select '4','-'select distinct DWDM from tb20091029_1 t
where not exists (
select 1 from tb20091029_1 where DWDM = t.DWDM and DM <> t.DM
)
----------
DWDM
2
4(2 件処理されました)----------
create table a(dwdm int, dm varchar(4));----循环插入8000000条记录行
declare @a int
set @a=1000000;
while(@a>0)
begin
insert into a(dwdm, dm)
select
1,'5' union all select
1,'-' union all select
2,'-' union all select
2,'-' union all select
3,'-' union all select
3,'s' union all select
3,'-' union all select
4,'-';
set @a=@a-1
end;--------8秒钟出数据
select dwdm
from a
group by dwdm
having count(dwdm)=sum(case dm when '-' then 1 else 0 end);--------10分钟了,还没出数据
select distinct DWDM from a t
where not exists (
select 1 from a where DWDM = t.DWDM and DM <> t.DM
)