一些常见的SQL面试题

有表testtb1

 
insert into testtb1
select 1 ,'jack',99union select 2 ,'mike',95union select 3 ,'lily',75union select 4 ,'lucy',95union select 5 ,'tony',80union select 6 ,'walt',95

id是主键，但不连续。

1，查询第3条记录

select id,name,score from 
(
select * ,num=ROW_NUMBER() over (order by id) from testtb1 
) m
where num=3

2,查询第2到第5条记录

with x as 
(
select * ,num=ROW_NUMBER() over (order by id) from testtb1 
)
select ID,name,score from x where num between 2 and 5

3，查询第2高分的所有人

with x as
(
select score=min(score) from 
(
select top 2 score from testtb1 group by score order by score desc
)m
)
select t.* from testtb1 t inner join x 
on t.score=x.score