一面试题

Question

有一学生表如下：
student（ID(学号)，分数）
要求选出分数排在第二位的学号（可以并列）
想了一下不知怎么解决。求高人告诉

Answer 1

select top 1 * from studenttable
where 分数<(select top 1 分数 from studenttable order by 分数)
order by 分数

Answer 2

select * from studenttable where 分数=(
select top 1 分数 from studenttable
where 分数<(select top 1 分数 from studenttable order by 分数 desc)
order by 分数 desc)
可并列

Answer 3

;with tt
as(
select dense_rank()over(order by 分数)[row_number],
       ID
from tb
)
select * from 
where )[row_number]=2

Answer 4

declare @t1 table(
    [id] varchar(3),
    [score] int)
    
insert into @t1
select '123','100'
union
select '122','99'
union
select '121','99'
union
select '120','99'
union
select '124','98'
select * from @t1
where score in
(
select top 1 score from @t1 
where score < 
(
select max(score) from @t1
)
)

Answer 5

 2005 以上有现成的 rank 等等 --2000 下面这个如果有两个第一名就 直接跳到第三名了
 select * from student a 
 where (select count(*) from student b where b.分数 > a.分数) = 1 

Answer 6

row_number=2 没有考虑到第一名的分数也可能并列

Answer 7

2005的排名函数里面 可以用dense_rank函数。可以并列的

Answer 8

select ID, Max(分数) from (select * from student where 分数 not in (select Max(分数) from student))先剔除最大的分数记录，然后再查找最大分数记录。

Answer 9

CREATE TABLE #
(
 id  INT,
 score INT
)INSERT INTO # 
SELECT  1,98
UNION ALL
SELECT  2,98
UNION ALL
SELECT  3,95
UNION ALL
SELECT  4,95
UNION ALL
SELECT  5,92SELECT * FROM #SELECT  * FROM  #
WHERE  score=(
 SELECT  TOP 1 score  FROM #
 WHERE  score <(SELECT TOP 1 score  FROM #   ORDER BY  score DESC )
 )/*
id score
3 95
4 95
*/;WITH t AS (
SELECT   DENSE_RANK()OVER ( ORDER  BY score DESC ) AS RANK ,*   FROM #  
)
SELECT * FROM t WHERE RANK=2/*
RANK id score
2 3 95
2 4 95
*/DROP TABLE  #

Answer 10

select * from student 
where 
score=(select  distinct top 1 score from student 
where score <(select max(score)as maxnum from student) order by score desc)
测试了没错，但是效率不保证

Answer 11

select id,score 
from t,
(select id,row_number(partition by score order by score desc) rownumber
from t) t1
where t.id = t1.id 
and rownumber = 2

Answer 12

select
   top 1 with ties id
from
   tb
where
   scores
in
   (select
      score
    from
      (select top 2 with ties * from tb order by score desc)t)

Answer 13

再来
;with leno as 
(
select dense_rank() over(order by score desc) rank ,id,score  from t
)
select * from leno where rank = 2

Answer 14

哈哈，连小F姐也来啦，看来我这个贴的帖名可以啦
不过这个确实是今天上午笔试的时候的一道题，他们要求是Oracle我不会，只会2000和2005

Answer 15

create table #student(ID int identity(1,1),分数 int)
insert #student
select 68 union all
select 67 union all
select 68 union all
select 69 union all
select 90 union all
select 81 union all
select 76 union all
select 75 union all
select 73 union all
select 86 union all
select 95 union all
select 67 union all
select 77 union all
select 88 union all
select 90select * from #student where 分数=
(select top 1 分数 from #student as s1 where 分数<>(select max(分数) from #student as s2) order by 分数 desc)select * from #student as s1 where 分数=
 (select top 1 * from (select distinct top 2 分数 from #student as s2 order by 分数 desc) as t order by 分数)

Answer 16

IF OBJECT_ID('tempdb..#temp')
   IS NOT NULL
DROP TABLE #temp1CREATE TABLE #temp 
(
  id int,
  score int
)INSERT INTO #temp 
SELECT 1,89 UNION ALL
SELECT 2,88 UNION ALL
SELECT 3,78 UNION ALL
SELECT 4,69 UNION ALL
SELECT 5,88 UNION ALL
SELECT 6,45 
SELECT id,score FROM #temp as a
INNER JOIN 
(
   SELECT  MAX(c.score) as sc FROM 
  (
      SELECT id,score  FROM  #temp
      WHERE id not in
             (SELECT id FROM #temp WHERE score =(SELECT MAX(score) FROM #temp)
       )
   ) c
) as  b ON a.score = b.sc 

Answer 17

--修改下
SELECT  id FROM #temp where score=
( 
  SELECT MAX(b.score) as sc FROM 
     ( SELECT id,score  FROM  #temp
       WHERE id not in
             (SELECT id FROM #temp WHERE score =(SELECT MAX(score) FROM #temp))
     ) b
) 

Answer 18

SELECT ID,分数 FROM student
WHERE 分数=(
SELECT TOP 1 分数 FROM student
WHERE 分数 < (SELECT TOP 1 分数  FROM student ORDER BY  分数 DESC )
ORDER BY 分数 DESC )

Answer 19

SELECT ID,分数 FROM student
WHERE 分数=(
SELECT TOP 1 分数 FROM student
WHERE 分数 < (SELECT TOP 1 分数 FROM student ORDER BY 分数 DESC )
ORDER BY 分数 DESC )

Answer 20

SELECT ID,分数 FROM student                                         --根据分数求出ID  
WHERE 分数=(
SELECT TOP 1 分数 FROM student                                      --求出第二名分数
WHERE 分数 < (SELECT TOP 1 分数  FROM student ORDER BY  分数 DESC )  --求出第一名分数
ORDER BY 分数 DESC )

Answer 21

select ID,分数 from 
(select ID,分数,
名次=(select count(*)+1 from tb where tb.id=a.id and a.分数>tb.分数) from tb a) b
where b.名次=2