求一个SQL语句的写法

[iranjn]

有数据集如下：
N W
2005001 0
2005002 0
2005003 0
2005200 1
2005201 1
2005202 1
我按照W分组想选出第二大的N的值。结果是
N W
2005002 0
2005201 1
请告诉我SQL怎么写

[xueguang]

--刚才写的不严谨,借用一下楼上的数据
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,2

SELECT 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

[vivianfdlpw]

如果同一个W不存在相同的N：

select * from 表 t
where (select count(1) from 表 where W=t.W and N>t.N)=1

[vivianfdlpw]

如果同一个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))

[iwl]

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
)

[xueguang]

SELECT MAX(N) N,W FROM 表 WHERE N NOT IN(SELECT MAX(N) FROM 表 GROUP BY W HAVING COUNT(*)>1) GROUP BY W

[zlp321002]

--写得复杂了点.促合看下!
--测试环境
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 行）

[lxp916]

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)