-->生成测试数据
declare @tb table([A] nvarchar(3),[B] nvarchar(1),[C] nvarchar(1))
Insert @tb
select N'D11',N'0',N'0' union all
select N'D11',N'0',N'1' union all
select N'D12',N'1',N'1' union all
select N'D12',N'1',N'1' union all
select N'D13',N'0',N'1' union all
select N'D13',N'0',N'1'
Select distinct * from @tb t
where not exists( select 1 from @tb where [B]=t.[B] and [C]=t.[C] and [A]>t.[A])
AND [B]=0 AND [C]=1
/*
A B C
---- ---- ----
D13 0 1
*/
declare @tb table([A] nvarchar(3),[B] nvarchar(1),[C] nvarchar(1))
Insert @tb
select N'D11',N'0',N'0' union all
select N'D11',N'0',N'1' union all
select N'D12',N'1',N'1' union all
select N'D12',N'1',N'1' union all
select N'D13',N'0',N'1' union all
select N'D13',N'0',N'1'
Select distinct * from @tb t
where not exists( select 1 from @tb where [B]=t.[B] and [C]=t.[C] and [A]>t.[A])
AND [B]=0 AND [C]=1
/*
A B C
---- ---- ----
D13 0 1
*/
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-01 23:13:00
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(A nvarchar(3),B nvarchar(1),C nvarchar(1))
Go
Insert into ta
select 'D11','0','0' union all
select 'D11','0','1' union all
select 'D12','1','1' union all
select 'D12','1','1' union all
select 'D13','0','1' union all
select 'D13','0','1'
Go
--Start
Select distinct *
from ta a
where not exists(select 1 from ta where a.a = a and (c <> 1 or b <> 0))
and c = 1 and b = 0
--Result:
/*
A B C
---- ---- ----
D13 0 1(所影响的行数为 1 行)
*/
--End
drop table test--test datacreate table test(
a varchar(10),
b int,
c int
)goinsert into test values('D11', 0, 0)
insert into test values('D11', 0, 1)
insert into test values('D12', 1, 1)
insert into test values('D12', 1, 1)
insert into test values('D13', 0, 1)
insert into test values('D13', 0, 1)--end of test data--startselect top 1 a, 0 as b, 1 as c
from test tt
where not exists (select * from test ta
where ta.a=tt.a
and (ta.b<>0 or ta.c<>1))
二楼的方法得出的结果是正确的,我另外写一个写法,测试结果正确select 'D11' as A ,0 as B,0 as C into #aaa
insert into #aaa values('D11',0,1 )
insert into #aaa values('D12',1,1 )
insert into #aaa values('D13',0,1 )
insert into #aaa values('D13',0,1 )select toop 1 * from #aaa where a=(
select a from #aaa t1
where t1.b=0 and t1.c=1
group by t1.a
having count(t1.a)=(select count(1) from #aaa where a=t1.a group by a))
二楼的方法得出的结果是正确的, 我另外写一个写法,测试结果正确 ,不过比 2 楼复杂一点,结果一样
select 'D11' as A ,0 as B,0 as C into #aaa
insert into #aaa values('D11',0,1 )
insert into #aaa values('D12',1,1 )
insert into #aaa values('D13',0,1 )
insert into #aaa values('D13',0,1 )select top 1 * from #aaa where a=(
select a from #aaa t1
where t1.b=0 and t1.c=1
group by t1.a
having count(t1.a)=(select count(1) from #aaa where a=t1.a group by a))
Select distinct * from @tb t
where not exists( select 1 from @tb where [B]=t.[B] and [C]=t.[C] and [A]>t.[A])
AND [B]=0 AND [C]=1
在去掉 not exists一句后,也只有两条记录,
Select distinct * from @tb t
where [B]=0 AND [C]=1
/*
A B C
---- ---- ----
D11 0 1
D13 0 1
*/然后在这个结果集上加上A列最大的条件限制,
怎么会出3条结果来?
疑惑中......
导致结果不对,hery2002 不好意思啊,呵呵
A B C
D11 0 0
D11 0 1
D12 1 1
D12 1 1
D13 0 1
D13 0 1
D14 0 1结果:
D13 0 1 只要一条记录、以A为组,B=0且C要都为1
D14 0 1
1楼的方法还是不能实现
declare @tb table([A] nvarchar(3),[B] nvarchar(1),[C] nvarchar(1))
Insert @tb
select N'D11',N'0',N'0' union all
select N'D11',N'0',N'1' union all
select N'D12',N'1',N'1' union all
select N'D12',N'1',N'1' union all
select N'D13',N'0',N'1' union all
select N'D13',N'0',N'1'
Select distinct * from @tb
where [B]=0 AND [C]=1
D11 0 1
D13 0 1
declare @tb table([A] nvarchar(3),[B] nvarchar(1),[C] nvarchar(1))
Insert @tb
select N'D11',N'0',N'0' union all
select N'D11',N'0',N'1' union all
select N'D12',N'1',N'1' union all
select N'D12',N'1',N'1' union all
select N'D13',N'0',N'1' union all
select N'D13',N'0',N'1' union all
select N'D14',N'0',N'1' Select distinct * from @tb t
where 1=(select sum(case when c=1 then 1 else 0 end)/count(*) from @tb
where a=t.a and b=0)D13 0 1
D14 0 1
不要只是看,不实践...
我9楼已经分析的很清楚了.不需要在解释吧.
declare @tb table([A] nvarchar(3),[B] nvarchar(1),[C] nvarchar(1))
Insert @tb
select N'D11',N'0',N'0' union all
select N'D11',N'0',N'1' union all
select N'D12',N'1',N'1' union all
select N'D12',N'1',N'1' union all
select N'D13',N'0',N'1' union all
select N'D13',N'0',N'1' union all
select N'D14',N'0',N'1'
Select distinct * from @tb t
where not exists( select 1 from @tb where [B]=t.[B] and [C]=t.[C] and [A]>t.[A])
AND [B]=0 AND [C]=1
/*
A B C
---- ---- ----
D14 0 1
*/
D14 0 1
我要的是
D13 0 1
D14 0 1
declare @tb table([A] nvarchar(3),[B] int,[C] int)
Insert @tb
select N'D11',N'0',N'0' union all
select N'D11',N'0',N'1' union all
select N'D12',N'1',N'1' union all
select N'D12',N'1',N'1' union all
select N'D13',N'0',N'1' union all
select N'D13',N'0',N'1' union all
select N'D14',N'0',N'1'
Select distinct * from @tb t
where b=0 and c=1 and [A] not in (select [A] from @tb where (b != 0 or c!=1))
/*
A B C
---- ----------- -----------
D13 0 1
D14 0 1
*/