数据库的面试题高手进来看看
表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))))
[解决办法]
select * from A as tmp
where (select count(distinct 工资) from A where 工资> =tmp.工资)=2
后面的数字可以随便改，改为1的话 可以得出5000的工资，改为2的话可以得出两个2000的工资，依次类推。
2楼的方法沒有道理.
[解决办法]
select * from A where 工资=(select Max(工资) from (select distinct top n 工资 from A order by 工资) as a)
n为你想要排的排名
[解决办法]
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 Where 工资=(Select max(工资) From A Where 工资 <> (Select max(工资) From A))
[解决办法]
select * from A as tmp
where (select count(distinct 工资) from A where 工资 >=tmp.工资)=2
我支持这种写法。
[解决办法]
ding
[解决办法]
select *,identity(int,1,1)as id into a from 工资表 group by 工资 order by 工资
select * from 工资表 where 工资=（select 工资 from a where id =2)
drop table a
写的有点多，那位有简单的，告知一下！
[解决办法]
select * from 工资=(select max(工资) from t where 工资 <( select max(工资) from t))
[解决办法]
SQLServer2005:
declare @t table(
name varchar(2),
salary int)
insert @t select 'a',2000
union all select 'b',1200
union all select 'c',2000
union all select 'd',5000
select
name,
salary
from(
select
name,
salary,
rank = dense_rank() over(order by salary desc)
from @t)a
where a.rank = 2
[解决办法]
declare @top varchar(10)
set @top='2'--排第几
declare @Sql varchar(1000)
set @Sql='select * from temp where salary in (select top 1 salary from (select distinct top '+@top+' salary from temp order by salary desc)a order by salary )'
exec (@Sql)
说明：
1、select distinct top '+@top+' salary from temp order by salary desc，取出工资值的最高2位；
2、select top 1 salary from (工资值的最高2位)a order by salary ，取出最高2位工资中低的那位，就是第2位；
3、select * from temp where salary in (第2位工资)，取出工资等于第2位工资数的人员记录。
ok了。
我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html
