你可以为增加个临时表,加一个字段identity:
select identity(int,1,1) aid,* into #T from 表
select a.id,a.je,a.flag,a.state from #T a where
aid in (select max(aid) from #T where flag<>-1 group by je)
drop table #T
实验如下:
--drop table #t
create table #t
(aid int identity(1,1),id int,je int,flag int,state int)
insert #t values(100,100,1,1)
insert #t values(100,100,-1,2)
insert #t values(100,100,1,4)
insert #t values(100,222,1,8)
insert #t values(100,555,1,0)
insert #t values(120,123,1,1)
insert #t values(123,11,1,1)
select a.id,a.je,a.flag,a.state from #t a
select a.id,a.je,a.flag,a.state from #t a where
aid in (select max(aid) from #t where flag<>-1 group by je)