数据结构如下:id | a1 | a2 | a31 12 12 R
2 12 12 E
3 13 13 R
4 13 14 E查询结果1 12 12 R
2 13 13 R
3 13 14 E规则如下:
当a3 列为R时, 选出当前记录
当a3 列为E时, 如果没有对应的R的记录,则选出,否则忽略
2 12 12 E
3 13 13 R
4 13 14 E查询结果1 12 12 R
2 13 13 R
3 13 14 E规则如下:
当a3 列为R时, 选出当前记录
当a3 列为E时, 如果没有对应的R的记录,则选出,否则忽略
where not exists(select 1 from ta where a3 = a.a3 and id > a.id)
or(a3='e' and cast(a1 as varchar)+cast(a2 as varchar)
not in(select cast(a1 as varchar)+cast(a2 as varchar)from table where a3='r'))
-------------
这里的对应是指?
where not exists(select 1 from ta where a1 = a.a1 and a3 > a.a3)
where not exists(select 1 from table where a3>a.a3 and a = a.a2 and a=a.a3)
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-10 16:59:25
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,a1 int,a2 int,a3 nvarchar(1))
Go
Insert into ta
select 1,12,12,'R' union all
select 2,12,12,'E' union all
select 3,13,13,'R' union all
select 4,13,14,'E'
Go
--Start
select * from ta a
where not exists(select 1 from ta where a2 = a.a2 and a3 > a.a3)--Result:
/*id a1 a2 a3
----------- ----------- ----------- ----
1 12 12 R
3 13 13 R
4 13 14 E(所影响的行数为 3 行)
*/
--End
create table #t123 (id int,a1 char(20),a2 char(20),a3 char(20))insert into #t123
select 1,'12','12','R' union all
select 2,'12','12','E' union all
select 3,'13','13','R' union all
select 4,'13','14','E'select * from #t123 a
where a3='R' or not exists(select 1 from #t123 where a3 = a.a3 and id > a.id)