一道SQL笔试题，困扰了好几天了

Question

有两张表：
Record(recNumber<主键>学号, recScore分数)
Student(stuNumber<主键>学号, stuName姓名, stuClass班级)
用SQL语句实现以下查询：
查询每一个班级中第二名的同学的姓名。

Answer 1

select recNumber,recScore,stuName,stuClass from
(
select t1.* , px = (select count(1) from
(
  select m.* , n.stuName, n.stuClass from Record m, Student n where m.recNumber = n.recNumber
) t1 where t1.stuClass = t2.stuClass and t1.recScore > t2.recScore
) + 1 from 
(
  select m.* , n.stuName, n.stuClass from Record m, Student n where m.recNumber = n.recNumber
) t2 
) t3
where px = 2
order by stuClass

Answer 2

SELECT MAX(RECSCORE) ,STUNAME,STUCLASS FROM
(SELECT RECSCORE ,STUNAME,STUCLASS FROM 
(SELECT MAX(RECSCORE) ,STUNAME,STUCLASS FROM RECORD A INNER JOIN STUDENT B ON A.RECNUMBER=B.STUNUMBER GROUP BY STUCLASS
UNION ALL 
SELECT RECSCORE ,STUNAME,STUCLASS FROM RECORD A INNER JOIN STUDENT B ON A.RECNUMBER=B.STUNUMBER)C
HAVING COUNT(*)=1)GROUP BY STUCLASS

Answer 3

也有错误：
服务器: 消息 156，级别 15，状态 1，行 6
在关键字 'GROUP' 附近有语法错误。

Answer 4

把t1换成t2,不好意思.select recNumber,recScore,stuName,stuClass from
(
select t2.* , px = (select count(1) from
(
  select m.* , n.stuName, n.stuClass from Record m, Student n where m.recNumber = n.recNumber
) t1 where t1.stuClass = t2.stuClass and t1.recScore > t2.recScore
) + 1 from 
(
  select m.* , n.stuName, n.stuClass from Record m, Student n where m.recNumber = n.recNumber
) t2 
) t3
where px = 2
order by stuClass

Answer 5

我的思路是：
第一步：查询出每个班第一名的记录列表
第二步：从所有记录中排除掉第一步查询出来的记录
第三步：从剩下的记录列表中查找出每个班当前的第一名的记录思路倒是有了，但是不知道正确不正确，而且最关键的是，我不会写这样的SQL语句，汗。

Answer 6

依然不行：
服务器: 消息 207，级别 16，状态 3，行 1
列名 'recNumber' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'recNumber' 无效。

Answer 7

--测试数据
create table Record(recNumber int,recScore decimal(10,2))
insert into Record 
select 1,65 union all
select 2,54 union all
select 3,656 union all
select 4,69
create table Student(stuNumber int,stuName nvarchar(20),stuClass nvarchar(20))
insert into Student
select 1,'张三','一班' union all
select 2,'李四','一班' union all
select 3,'王五','二班' union all
select 4,'赵六','二班' 
select stuClass,stuName from( select rn = row_number() over(partition by b.stuClass order by a.recScore desc)
,b.stuName,b.stuClass
from Record a,Student b
where a.recNumber = b.stuNumber) a
where rn =2

Answer 8

服务器: 消息 195，级别 15，状态 10，行 1
'row_number' 不是可以识别的 函数名。

Answer 9

/*
Record(recNumber <主键>学号, recScore分数)
Student(stuNumber <主键>学号, stuName姓名, stuClass班级) 
*/Use Test
Go
If Object_id('Record','U') Is Not null
Drop Table Record
If Object_id('Student','U') Is Not null
Drop Table StudentGo
Create Table Student(stuNumber int Identity(1,1) not null,stuName nvarchar(50),stuClass nvarchar(50),Constraint PK_Student Primary Key(stuNumber Asc))
Create Table Record(id int Identity(1,1) not null,recNumber int not null,recScore int,Constraint PK_Record Primary Key(ID Asc),Constraint FK_Record_recNumber Foreign Key(recNumber) References Student(stuNumber))
Go
Insert Into Student (stuName,stuClass)
Select N'Name1',N'A' Union All
Select N'Name2',N'A' Union All
Select N'Name3',N'A' Union All
Select N'Name4',N'B' Union All
Select N'Name5',N'B' Union All
Select N'Name6',N'B'
Insert Into Record(recNumber,recScore)
Select 1,89 Union All
Select 2,76 Union All
Select 3,100 Union All
Select 4,67 Union All
Select 5,88 Union All
Select 6,84 
--2000
Select a.* 
From Student a
inner Join Record b On b.recNumber=a.stuNumber 
Where a.stuNumber In (Select Top 2 x.stuNumber
From Student x
inner Join Record y On y.recNumber=x.stuNumber And x.stuClass=a.stuClass  Order By y.recScore Desc)
And a.stuNumber <>(Select Top 1 x.stuNumber
From Student x
inner Join Record y On y.recNumber=x.stuNumber And x.stuClass=a.stuClass  Order By y.recScore Desc)
--2005
;With t As
(
Select Row=Row_number() Over(Partition By a.stuClass Order by b.recScore Desc),a.* 
From Student a
inner Join Record b On b.recNumber=a.stuNumber 
)
Select stuNumber,stuName,stuClass From t Where Row=2
/*
stuNumber stuName stuClass
---------------------------
1 Name1 A
6 Name6 B
*/

Answer 10

楼主朋友,可能如果一步想得出结果的话,得需要嵌套多个子查询,这样非常容易出错.为了方便查看,我定义了一个表变量,它来记录下初次查询后的结果:
declare @temp table(stuNumber int,stuName nvarchar(20),recScore int,stuClass nvarchar(20))
然后把2个原表按照学号连接成一个表,放入表变量@temp中;
insert @temp
(select s.stuNumber,s.stuName,r.recScore,s.stuClass from Student s left join Record r on s.stuNumber=r.recNumber)
接下来就可以直接使用这个表变量查询了:
select tb1.stuName
from 
  (select 
     tb.*
     ,pl=(select 1+count(1)
          from @temp
          where stuClass=tb.stuClass and recScore>tb.recScore)
   from
     @temp tb
  ) tb1 
where 
    tb1.pl=2如果楼主想一步到位那么请用下面的代码:
select tb1.stuName
from 
  (select 
     tb.*
     ,pl=(select 1+count(1)
          from (select s.stuNumber,s.stuName,r.recScore,s.stuClass from @Student s left join @Record r on s.stuNumber=r.recNumber) tb3
          where tb3.stuClass=tb.stuClass and tb3.recScore>tb.recScore)
   from
    (select s.stuNumber,s.stuName,r.recScore,s.stuClass from @Student s left join @Record r on s.stuNumber=r.recNumber) tb
  ) tb1 
where 
    tb1.pl=2

Answer 11

 create table Record(recNumber int,recScore decimal(10,2))
insert into Record 
select 1,65 union all
select 2,54 union all
select 3,656 union all
select 4,69 create table Student(stuNumber int,stuName nvarchar(20),stuClass nvarchar(20))
insert into Student
select 1,'张三','一班' union all
select 2,'李四','一班' union all
select 3,'王五','二班' union all
select 4,'赵六','二班'
------------------------------------------------------------------------
select  stuclass,max(recscore)fenshu,stuName from (select recScore ,stuclass,stuname  from record as a inner join 
student as b 
on a.recnumber = b.stunumber)q where recscore not in(
select max(recscore)maxv  from(
select recScore ,stuclass,stuname  from record as a inner join 
student as b 
on a.recnumber = b.stunumber)c group by stuclass) group by stuclass,stuname

Answer 12

借用16樓的數據create table Record(recNumber int,recScore decimal(10,2))
insert into Record 
select 1,65 union all
select 2,54 union all
select 3,656 union all
select 4,69 union all
select 5,100 union all
select 6,80 create table Student(stuNumber int,stuName nvarchar(20),stuClass nvarchar(20))
insert into Student
select 1,'张三','一班' union all
select 2,'李四','一班' union all
select 3,'王五','二班' union all
select 4,'赵六','二班' union all
select 5,'劉三','三班' union all
select 6,'楊二','三班'
select *,seq=identity(int,1,1) into # from Record join Student on stuNUmber=recNumber order by stuClass,recScore descselect stuClass,stuNumber,stuName,recScore from (
select *,ord=seq-(select count(*) from # as a where stuClass<b.stuClass) from # as b
)  c
where ord=2 
order by stuClass,stuNumber
drop table Record 
drop table Student
drop table #

Answer 13

/*
stuClass             stuNumber   stuName              recScore     
-------------------- ----------- -------------------- ------------ 
一班                   2           李四                   54.00
二班                   4           ?六                   69.00
三班                   6           楊二                   80.00(3 row(s) affected)
*/

Answer 14

按楼主的意思写:
create table Record(recNumber int,recScore decimal(10,2))
insert into Record 
select 1,65 union all
select 2,54 union all
select 3,656 union all
select 4,69 union all
select 5,100 union all
select 6,80 union all
select 7,69 union all
select 8,65 union all
select 9,54
create table Student(stuNumber int,stuName nvarchar(20),stuClass nvarchar(20))
insert into Student
select 1,'张三','一班' union all
select 2,'李四','一班' union all
select 3,'王五','二班' union all
select 4,'赵六','二班' union all
select 5,'劉三','三班' union all
select 6,'楊二','三班' union all
select 7,'刘大','三班' union all
select 8,'陈小','三班' union all
select 9,'沈二','三班'select T4.stuNumber,T4.stuName,T4.stuClass,T3.maxscore as stuScore from (
select d.stuclass, max(c.recscore) as maxscore from record c inner join student d on d.stuNumber=c.recNumber where c.recNumber not in ( --获取班级及第二名分数
select T2.stuNumber from ( --获取各班第一名学号
select b.stuclass, max(a.recscore) as maxscore from Record a inner join Student b on b.stuNumber=a.recNumber group by b.stuClass --获取班级及第一名分数
) T1 inner join (
select a.stuNumber,a.stuName,a.stuclass,b.recScore from student a inner join record b on a.stunumber=b.recnumber
)T2 on T1.stuClass=T2.stuClass and T1.maxscore=T2.recScore
) group by d.stuClass
)T3 inner join (
select a.stuNumber,a.stuName,a.stuclass,b.recScore from student a inner join record b on a.stunumber=b.recnumber
)T4 on T3.stuClass=T4.stuClass and T3.maxscore=t4.recscore
go
drop table record,student
/*
stuNumber   stuName              stuClass             stuScore
----------- -------------------- -------------------- ---------------------------------------
2           李四                   一班                   54.00
4           赵六                   二班                   69.00
6           楊二                   三班                   80.00
*/

Answer 15

有两张表： 
Record(recNumber <主键>学号, recScore分数) 
Student(stuNumber <主键>学号, stuName姓名, stuClass班级) 
用SQL语句实现以下查询： 
查询每一个班级中第二名的同学的姓名。我想了一种不用存储过程,也不用中间表的办法,供参考!
select stuName姓名
from Record join Student on recNumber = stuNumber 
where CAST(max(recScore分数) as varchar) + '_' stuClass班级 = (
select CAST(max(recScore分数) as varchar) + '_' stuClass班级 as maxrecScore_stuClass
from Record join Student on recNumber = stuNumber )  a 
where a.maxrecScore_stuClass not in  (select CAST(max(recScore分数) as varchar) + '_' stuClass班级 as    maxrecScore_stuClass
from Record join Student on recNumber = stuNumber 
group by stuClass班级
)

Answer 16

以上是假设stuClass班级不是数值型的字段.如果是需要转化下CAST(max(stuClass班级) as varchar)

Answer 17

select stuName姓名 
from Record join Student on recNumber = stuNumber 
where CAST(max(recScore分数) as varchar) + '_' stuClass班级 = ( 
select CAST(max(recScore分数) as varchar) + '_' stuClass班级 as maxrecScore_stuClass 
from Record join Student on recNumber = stuNumber )  a 
where a.maxrecScore_stuClass not in  (select CAST(max(recScore分数) as varchar) + '_' stuClass班级 as    maxrecScore_stuClass 
from Record join Student on recNumber = stuNumber 
group by stuClass班级 
) 请改为:SELECT Student.stuName姓名
FROM Record INNER JOIN
      Student ON Record.recNumber = Student.stuNumber
WHERE ((CAST(Record.recScore分数 AS varchar) + '_' + Student.stuClass班级) IN
          (SELECT CAST(MAX(Record.recScore分数) AS varchar) 
               + '_' + Student.stuClass班级 AS maxrecScore_stuClass
         FROM Record INNER JOIN
               Student ON Record.recNumber = Student.stuNumber
         WHERE ((CAST(Record.recScore分数 AS varchar) + '_' + Student.stuClass班级) 
               NOT IN
                   (SELECT CAST(MAX(Record.recScore分数) AS varchar) 
                        + '_' + Student.stuClass班级 AS maxrecScore_stuClass
                  FROM Record INNER JOIN
                        Student ON Record.recNumber = Student.stuNumber
                  GROUP BY Student.stuClass班级))
         GROUP BY Student.stuClass班级))以上语句经过了测试无误!

Answer 18

晕，CSDN的论坛有问题啊，没引用完全。
14楼的答案是正解。

Answer 19

恕我学识浅薄，没看懂-_-!!---------------另，分步进行查询的代码也无法运行，会报错：服务器: 消息 156，级别 15，状态 1，行 4
在关键字 'select' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 4
第 4 行: ')' 附近有语法错误。---------------另，我把你说的一步到位的代码拿到SQL SERVER 2000中，无法运行：服务器: 消息 137，级别 15，状态 2，行 6
必须声明变量 '@Student'。
服务器: 消息 137，级别 15，状态 1，行 9
必须声明变量 '@Student'。