select Student_No from (select id=row_number()over(order by Student_Score) from Student_Score)t where id=2
select Student_No from (select id=row_number()over(order by Student_Score),* from Student_Score)t where id=2
SELECT TOP 1 * FROM ( SELECT TOP 2 * FROM ( SELECT Student_No,Student_Name, SUM(Student_Score )AS TOTAL FROM Student_Score GORUP BY Student_No,Student_Name )AS T ORDER BY TOTAL DESC )AS T1
SELECT *,Place=(SELECT COUNT(Student_Score) FROM Student_Score WHERE Student_Score>=a.Student_Score)+1 FROM tb a where Place=2 ORDER BY Place
select Student_No from Student_Score where Student_Score<> max(Student_Score) and Student_Score in( (select distinct,top 2 Student_Score from Student_Score order by Student_Score desc) )
如果不考虑并列第一,则: SELECT cid=identity(int,1,1),* into # FROM Student_Score order by Student_score desc select student_no from # where cid=2
如果考虑并列第一和并列第二,则: select student_no from student_score where student_score in( select top 1 student_score from( select top 2 student_score from student_score order by student_score desc )order by student_score )
修改下8楼的方法; 正解: 方法1. select Student_No from Student_Score where Student_Score in (select distinct top 2 Student_Score from Student_Score order by Student_Score desc )and Student_Score<> (select max(Student_Score) from Student_Score );
方法2. select Student_No from (select Student_Score.*, row_number() over(order by Student_Score desc) rank from Student_Score) where rank=2; 方法3. select Student_No from (select Student_Score.*, dense_rank() over(order by Student_Score desc) rank from Student_Score) where rank=2; 方法2和方法3的区别是: 方法2会排除重复的值,而方法3不会, 即:当有并列第二的情况时,方法3会全部返回出来,而方法2则只会返回一条数据.
select
Student_No
from
(select id=row_number()over(order by Student_Score) from Student_Score)t
where
id=2
Student_No
from
(select id=row_number()over(order by Student_Score),* from Student_Score)t
where
id=2
(
SELECT TOP 2 * FROM
(
SELECT Student_No,Student_Name,
SUM(Student_Score )AS TOTAL FROM Student_Score GORUP BY Student_No,Student_Name
)AS T
ORDER BY TOTAL DESC
)AS T1
*,Place=(SELECT COUNT(Student_Score) FROM Student_Score WHERE Student_Score>=a.Student_Score)+1
FROM
tb a
where
Place=2
ORDER BY
Place
and Student_Score in(
(select distinct,top 2 Student_Score from Student_Score order by Student_Score desc)
)
SELECT cid=identity(int,1,1),* into # FROM Student_Score order by Student_score desc
select student_no from # where cid=2
select student_no from student_score where student_score in(
select top 1 student_score from(
select top 2 student_score from student_score order by student_score desc
)order by student_score
)
正解:
方法1. select Student_No from Student_Score where Student_Score in
(select distinct top 2 Student_Score from Student_Score
order by Student_Score desc )and Student_Score<>
(select max(Student_Score) from Student_Score );
方法2. select Student_No from
(select Student_Score.*, row_number() over(order by Student_Score desc) rank from Student_Score)
where rank=2; 方法3. select Student_No from
(select Student_Score.*, dense_rank() over(order by Student_Score desc) rank from Student_Score)
where rank=2;
方法2和方法3的区别是:
方法2会排除重复的值,而方法3不会,
即:当有并列第二的情况时,方法3会全部返回出来,而方法2则只会返回一条数据.