数据库的面试题高手进来看看
表A
有两列   姓名   ，工资

            小王         2000
            小张         1200
            小非         2000
            小赵         5000

问题是找出工资排第二的人（包含重复）。


------解决方案--------------------
create table #a(姓名 varchar(8),工资 int)
insert #a select '小王 ', '2000 ' union select '小张 ', '1200 '
union select '小非 ', '2000 ' union select '小赵 ', '5000 '

select * from #a where 工资 = (
select top 1 工资 from #a where 工资 not in (select max(工资) from #a) order by 工资 desc )

drop table #a
------解决方案--------------------
--try


create table A(姓名 nvarchar(10),工资 int)
insert A select '小王 ', 2000
union all select '小张 ', 1200
union all select '小非 ', 2000
union all select '小赵 ', 5000


select * from A as tmp
where (select count(distinct 工资) from A where 工资 <=tmp.工资)=2
------解决方案--------------------
SELECT * FROM A
WHERE 工资 =( SELECT MAX(工资) FROM A
WHERE 工资 < (SELECT MAX(工资) FROM A ) )
------解决方案--------------------
select * from #a where 工资= (select max(工资)from ( select 工资 from #a where 工资 < (select max(工资) from #a )) a)
------解决方案--------------------
select 姓名,工资 from
(
SELECT * , px=(SELECT COUNT(工资) FROM a WHERE Score > b.工资) + 1 FROM a b
) t
where px = 2
------解决方案--------------------
select * from A where 工资 in
(select top 1 * from
(select distinct top 2 工资 from A order by 工资) as B
order by 工资 desc )
------解决方案--------------------
select *
from a
where 工资 =
(
select max(工资)
from a
where 工资 <
(select max(工资) from a)
)
------解决方案--------------------
select * from A as tmp
where (select count(distinct 工资) from A where 工资> =tmp.工资)=2
-------------------

二楼的是写反了，你自己都改过来了，应该不难理解吧：

> =tmp.工资额有2种，那么“> ”的一种就是最高工资，=那种也就是tmp.工资是第二高的。

------解决方案--------------------
有两列 姓名 ，工资

小王 2000
小张 1200
小非 2000
小赵 5000

问题是找出工资排第二的人（包含重复）。
select top 1 * from table1 where name not in(select top 1 name from table1 order by 工资 desc) order by 工资 desc
------解决方案--------------------
create table A(姓名 nvarchar(10),工资 int)
insert A select '小王 ', 2000
union all select '小张 ', 1200
union all select '小非 ', 2000
union all select '小赵 ', 5000

select top 1 with ties * from (select top 2 with ties * from A order by 工资 DESC) T order by 工资

------解决方案--------------------
select * from A where A.工资 = (select min(工资) from (select top 2 * from
(select distinct 工资 from A order by 工资 DESC))))