SE1 的SELECT * FROM students t1 WHERE name IN ( SELECT TOP 10 name FROM students t2 WHERE t1.classid=t2.classid ORDER BY score desc ) ORDER BY classid,score desc应该是正解,name若是唯一的话
表:students 字段: ClassID(班级ID) Name(学生名) Score(成绩)id | ClassID | Name | Score 1 1 A 100 2 1 B 95 3 1 C 93 4 2 D 60 5 2 E 88 6 2 F 92 7 3 G 78 8 3 H 30 9 3 I 60==================================== 这是测试的数据 列出每个班成绩前 2 名的同学 得出结果应该是:id | ClassID | Name | Score 1 1 A 100 2 1 B 95 6 2 F 92 5 2 E 88 7 3 G 78 9 3 I 60====================================可以不考虑成绩并列的情况 但学生名是有可能重复的
Declare @students Table (id int Identity(1,1),ClassID int,Name varchar(10),Score int) Insert @students values (1,'A',100) Insert @students values (1,'B', 95) Insert @students values (1,'C', 93) Insert @students values (2,'D', 60) Insert @students values (2,'E', 88) Insert @students values (2,'F', 92) Insert @students values (3,'G', 78) Insert @students values (3,'H', 30) Insert @students values (3,'I', 60) Select * from @students A where id in (Select top 2 id from @students where ClassID = A.ClassID order by Score desc) order by ClassID,Name,Score
以下為測試結果: id ClassID Name Score ----------- ----------- ---------- ----------- 1 1 A 100 2 1 B 95 5 2 E 88 6 2 F 92 7 3 G 78 9 3 I 60
select * from students t where t.id in (select top 2 t1.id from students t1 where t1.ClassID in (select distinct ClassId from students t3) and t1.ClassID = t.ClassID order by t1.Score desc)
经tommysun()的修改后, 正解为: Select * from student A where id in (Select top 2 id from student where ClassID = A.ClassID order by Score desc) order by ClassID,Score DESC
不行
错误
FROM students t1
WHERE name IN
(
SELECT TOP 10 name
FROM students t2
WHERE t1.classid=t2.classid
ORDER BY score desc
)
ORDER BY classid,score desc应该是正解,name若是唯一的话
字段:
ClassID(班级ID)
Name(学生名)
Score(成绩)id | ClassID | Name | Score
1 1 A 100
2 1 B 95
3 1 C 93
4 2 D 60
5 2 E 88
6 2 F 92
7 3 G 78
8 3 H 30
9 3 I 60====================================
这是测试的数据
列出每个班成绩前 2 名的同学
得出结果应该是:id | ClassID | Name | Score
1 1 A 100
2 1 B 95
6 2 F 92
5 2 E 88
7 3 G 78
9 3 I 60====================================可以不考虑成绩并列的情况
但学生名是有可能重复的
Insert @students values (1,'A',100)
Insert @students values (1,'B', 95)
Insert @students values (1,'C', 93)
Insert @students values (2,'D', 60)
Insert @students values (2,'E', 88)
Insert @students values (2,'F', 92)
Insert @students values (3,'G', 78)
Insert @students values (3,'H', 30)
Insert @students values (3,'I', 60)
Select * from @students A where id in (Select top 2 id from @students where ClassID = A.ClassID order by Score desc)
order by ClassID,Name,Score
id ClassID Name Score
----------- ----------- ---------- -----------
1 1 A 100
2 1 B 95
5 2 E 88
6 2 F 92
7 3 G 78
9 3 I 60
(select top 2 t1.id from students t1 where t1.ClassID in (select distinct ClassId from students t3) and t1.ClassID = t.ClassID order by t1.Score desc)
正解为:
Select * from student A where id in (Select top 2 id from student where ClassID = A.ClassID order by Score desc)
order by ClassID,Score DESC