select * from tab A where not exists(select 1 from tab where charindex(b,'5DX,ICT,BTF,FQC')>charindex(A.b,'5DX,ICT,BTF,FQC') and C<A.C)
只要总共只有'5DX,ICT,BTF,FQC'四种状态,上面一定行. 不论是否每种都有.
上面少了个条件,错了. select * from tab A where not exists(select 1 from tab where a=A.a and charindex(b,'5DX,ICT,BTF,FQC')>charindex(A.b,'5DX,ICT,BTF,FQC') and C<A.C)
create table #temp ( a char(15), b char(3), c datetime )goinsert into #temp values('JG04XCA279A','ICT','2003-11-20 02:32:25') insert into #temp values('JG04XCA279A','5DX','2003-11-20 02:40:57') insert into #temp values('JG04XCA279A','BFT','2003-11-23 05:03:03') insert into #temp values('JG04XCA279A','FQC','2003-11-23 06:15:26') insert into #temp values('JG04XCA279B','5DX','2003-11-22 06:16:14') insert into #temp values('JG04XCA279B','ICT','2003-11-23 04:38:33') insert into #temp values('JG04XCA279B','BFT','2003-11-23 05:05:09') insert into #temp values('JG04XCA279B','FQC','2003-11-23 05:05:09')go select distinct a from (select aa.a from #temp aa,#temp bb where aa.a=bb.a and aa.c>bb.c and ((aa.b='5DX' and bb.b='ICT') or (aa.b='5DX' and bb.b='BTF') or (aa.b='5DX' and bb.b='FQC') or (aa.b='ICT' and bb.b='BTF') or (aa.b='ICT' and bb.b='FQC') or (aa.b='BTF' and bb.b='FQC')) ) ttdrop table #temp
teaism老兄﹐我不明白你的tab A 和 select l from tab 中的l 指什幺啊﹗还有最后的C<A.C也不明白﹐能否请你说的详细一点﹐谢谢了。
eru 請問你的語句里的aa 和bb這兩個表分別指什么呢?謝謝﹗
tab A --A指查询中的表别名指TAB, select l from tab 这是子查询中常用的,一般是指查找记录是否存在。
drop table #tempcreate table #temp ( a char(15), b char(3), c datetime )goinsert into #temp values('JG04XCA279A','ICT','2003-11-20 02:32:25') insert into #temp values('JG04XCA279A','5DX','2003-11-20 02:40:57') insert into #temp values('JG04XCA279A','BFT','2003-11-23 05:03:03') insert into #temp values('JG04XCA279A','FQC','2003-11-23 06:15:26') insert into #temp values('JG04XCA279B','5DX','2003-11-22 06:16:14') insert into #temp values('JG04XCA279B','ICT','2003-11-23 04:38:33') insert into #temp values('JG04XCA279B','BFT','2003-11-23 05:05:09') insert into #temp values('JG04XCA279B','FQC','2003-11-23 05:05:09')select * from #temp where a not in (select A.a,* from #temp A,#temp B where A.a=B.a and charindex(A.b,'5DX,ICT,BTF,FQC')>charindex(B.b,'5DX,ICT,BTF,FQC') and A.C<B.C )这个才是对,刚才没调试。上面没有符合条件的记录。
select * from #temp where a not in (select A.a from #temp A,#temp B where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC') and A.C>B.C )
你多测测下面语句就明白了drop table #tempcreate table #temp ( a char(15), b char(3), c datetime )goinsert into #temp values('JG04XCA279A','ICT','2003-11-20 02:32:25') insert into #temp values('JG04XCA279A','5DX','2003-11-20 02:40:57') insert into #temp values('JG04XCA279A','BFT','2003-11-23 05:03:03') insert into #temp values('JG04XCA279A','FQC','2003-11-23 06:15:26') insert into #temp values('JG04XCA279B','5DX','2003-11-22 06:16:14') insert into #temp values('JG04XCA279B','ICT','2003-11-23 04:38:33') insert into #temp values('JG04XCA279B','BFT','2003-11-23 05:05:09') insert into #temp values('JG04XCA279B','FQC','2003-11-23 05:05:09')select * from #temp where a not in (select A.a from #temp A,#temp B where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC') and A.C>B.C )
select A.a from #temp A,#temp B where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC') and A.C>B.C 注释: 1. charindex(A.b,'5DX,ICT,BFT,FQC') 找出B列在里面的位置。 2.上面整句找出不符合你条件的A列值 最后 select * from #temp where a not in (select A.a from #temp A,#temp B where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC') and A.C>B.C ) 加上NOT IN关健字,就是你要记录了。
from tab A
where not exists(select 1
from tab
where charindex(b,'5DX,ICT,BTF,FQC')>charindex(A.b,'5DX,ICT,BTF,FQC')
and C<A.C)
不论是否每种都有.
select *
from tab A
where not exists(select 1
from tab
where a=A.a and charindex(b,'5DX,ICT,BTF,FQC')>charindex(A.b,'5DX,ICT,BTF,FQC')
and C<A.C)
(
a char(15),
b char(3),
c datetime
)goinsert into #temp values('JG04XCA279A','ICT','2003-11-20 02:32:25')
insert into #temp values('JG04XCA279A','5DX','2003-11-20 02:40:57')
insert into #temp values('JG04XCA279A','BFT','2003-11-23 05:03:03')
insert into #temp values('JG04XCA279A','FQC','2003-11-23 06:15:26')
insert into #temp values('JG04XCA279B','5DX','2003-11-22 06:16:14')
insert into #temp values('JG04XCA279B','ICT','2003-11-23 04:38:33')
insert into #temp values('JG04XCA279B','BFT','2003-11-23 05:05:09')
insert into #temp values('JG04XCA279B','FQC','2003-11-23 05:05:09')go
select distinct a from
(select aa.a from #temp aa,#temp bb
where aa.a=bb.a and aa.c>bb.c
and ((aa.b='5DX' and bb.b='ICT')
or (aa.b='5DX' and bb.b='BTF')
or (aa.b='5DX' and bb.b='FQC')
or (aa.b='ICT' and bb.b='BTF')
or (aa.b='ICT' and bb.b='FQC')
or (aa.b='BTF' and bb.b='FQC'))
) ttdrop table #temp
select l from tab 这是子查询中常用的,一般是指查找记录是否存在。
(
a char(15),
b char(3),
c datetime
)goinsert into #temp values('JG04XCA279A','ICT','2003-11-20 02:32:25')
insert into #temp values('JG04XCA279A','5DX','2003-11-20 02:40:57')
insert into #temp values('JG04XCA279A','BFT','2003-11-23 05:03:03')
insert into #temp values('JG04XCA279A','FQC','2003-11-23 06:15:26')
insert into #temp values('JG04XCA279B','5DX','2003-11-22 06:16:14')
insert into #temp values('JG04XCA279B','ICT','2003-11-23 04:38:33')
insert into #temp values('JG04XCA279B','BFT','2003-11-23 05:05:09')
insert into #temp values('JG04XCA279B','FQC','2003-11-23 05:05:09')select * from #temp where a not in (select A.a,*
from #temp A,#temp B
where A.a=B.a and charindex(A.b,'5DX,ICT,BTF,FQC')>charindex(B.b,'5DX,ICT,BTF,FQC')
and A.C<B.C
)这个才是对,刚才没调试。上面没有符合条件的记录。
from #temp A,#temp B
where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC')
and A.C>B.C
)
(
a char(15),
b char(3),
c datetime
)goinsert into #temp values('JG04XCA279A','ICT','2003-11-20 02:32:25')
insert into #temp values('JG04XCA279A','5DX','2003-11-20 02:40:57')
insert into #temp values('JG04XCA279A','BFT','2003-11-23 05:03:03')
insert into #temp values('JG04XCA279A','FQC','2003-11-23 06:15:26')
insert into #temp values('JG04XCA279B','5DX','2003-11-22 06:16:14')
insert into #temp values('JG04XCA279B','ICT','2003-11-23 04:38:33')
insert into #temp values('JG04XCA279B','BFT','2003-11-23 05:05:09')
insert into #temp values('JG04XCA279B','FQC','2003-11-23 05:05:09')select * from #temp where a not in (select A.a
from #temp A,#temp B
where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC')
and A.C>B.C
)
select A.a
from #temp A,#temp B
where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC')
and A.C>B.C
注释:
1. charindex(A.b,'5DX,ICT,BFT,FQC') 找出B列在里面的位置。
2.上面整句找出不符合你条件的A列值
最后
select * from #temp where a not in (select A.a
from #temp A,#temp B
where A.a=B.a and charindex(A.b,'5DX,ICT,BFT,FQC')<charindex(B.b,'5DX,ICT,BFT,FQC')
and A.C>B.C
)
加上NOT IN关健字,就是你要记录了。