如果同一个W存在相同的N:select * from 表 t where N=(select max(N) from 表 where W=t.W and N<(select max(N) from 表 where W=t.W))
select max(n) from tablename t where t1.w=t.w and t21.n<(select max(n) from tablename t2 where t2.w=t.w )
SELECT MAX(N) N,W FROM 表 WHERE N NOT IN(SELECT MAX(N) FROM 表 GROUP BY W HAVING COUNT(*)>1) GROUP BY W
--写得复杂了点.促合看下! --测试环境 declare @t table (N int ,W bit) insert into @t select 2005001,0 union all select 2005002,0 union all select 2005003,0 union all select 2005200,1 union all select 2005201,1 union all select 2005202,1 --查询 select * from ( select * from @t a where exists (select 1 from @t where W=a.W and N>a.N) ) A where not exists (select 1 from (select * from @t a where exists (select 1 from @t where W=a.W and N>a.N) ) B where B.W=A.W and B.N>A.N) --结果 N W ----------- ---- 2005002 0 2005201 1(所影响的行数为 2 行)
--刚才写的不严谨,借用一下楼上的数据 declare @t table (N int ,W INT) insert into @t select 2005001,0 union all select 2005002,0 union all select 2005003,0 union all select 2005200,1 union all select 2005201,1 union all select 2005202,1 union all select 2005202,2SELECT MAX(N) N,W FROM @t WHERE CONVERT(VARCHAR(10),N)+CONVERT(VARCHAR(10),W) NOT IN(SELECT CONVERT(VARCHAR(10),MAX(N))+CONVERT(VARCHAR(10),W) FROM @t GROUP BY W HAVING COUNT(*)>1) GROUP BY W --结果 N W ----------- ----------- 2005002 0 2005201 1 2005202 2
select max(E.N),E.w from E ,(select max(N) as N,w from E group by w) T where T.N>E.N and E.W=T.W group by E.w
select * from @t where n in (select max(N) as N from @t where N not in (select max(n) as N from @t group by w ) group by w)
where N=(select max(N)
from 表
where W=t.W
and N<(select max(N)
from 表
where W=t.W))
from tablename t
where t1.w=t.w
and t21.n<(select max(n)
from tablename t2
where t2.w=t.w
)
--测试环境
declare @t table (N int ,W bit)
insert into @t select 2005001,0
union all select 2005002,0
union all select 2005003,0
union all select 2005200,1
union all select 2005201,1
union all select 2005202,1
--查询
select * from ( select * from @t a
where exists (select 1 from @t where W=a.W and N>a.N) ) A
where not exists (select 1 from
(select * from @t a
where exists (select 1 from @t where W=a.W and N>a.N)
) B where B.W=A.W and B.N>A.N)
--结果
N W
----------- ----
2005002 0
2005201 1(所影响的行数为 2 行)
declare @t table (N int ,W INT)
insert into @t select 2005001,0
union all select 2005002,0
union all select 2005003,0
union all select 2005200,1
union all select 2005201,1
union all select 2005202,1
union all select 2005202,2SELECT MAX(N) N,W FROM @t WHERE CONVERT(VARCHAR(10),N)+CONVERT(VARCHAR(10),W) NOT IN(SELECT CONVERT(VARCHAR(10),MAX(N))+CONVERT(VARCHAR(10),W) FROM @t GROUP BY W HAVING COUNT(*)>1) GROUP BY W
--结果
N W
----------- -----------
2005002 0
2005201 1
2005202 2
where T.N>E.N and E.W=T.W
group by E.w