Question

求一条SQL语句(SQL Server2008)

已知有2张表，一个学生表,字段（ID,name，age），
另一个成绩表，字段（ID,subject，score）统计每门功课前两名学生的ID，name ,subject ,score
假设学生表是a,成绩表是b
请写出完整的SQL语句，最好测试过

Answer 1

设学生表为student，成绩表为score，其他字段跟你缺搏敏列的银岩相伏枝同
select c.* from
(select a.id,a.name,b.subject,b.score from student a,score b where a.id=b.id) c
where name in (select top 2 d.name from (select a.id,a.name,b.subject,b.score from student a,score b where a.id=b.id) d
where d.subject=c.subject order by d.score desc)
order by subject

Answer 2

select top 2 s.id, name, subject, score
from
student as s
join score as sc
on s.id = sc.id
group by subject

Answer 3

select 学生罩兆.ID，name ,subject ,score
from 学生态闷森，成绩
where 学生.ID=成绩.ID AND
group by subject having rank(score)<=2;

回答楼上帆亩的：成绩的码应该是2个，这里的ID是外码。

Answer 4

select top 2 name,subject,score
from 学配卜生，成樱耐绩培颂穗
where 学生.id=成绩.id
order by score desc

Answer 5

SELECT T.*,X.NAME FROM 成绩返晌表 T INNER JOIN 学漏亏锋生表 X
ON T.ID=X.ID
WHERE (SELECT COUNT(1) FROM 成绩表 WHERE SUBJECT=T.SUBJECT AND T.SCORE<SCORE )<空举2