SQL练手

CREATE TABLE StudentGrade(
stuId CHAR(4),    --学号
subId INT,        --课程号
grade INT,        --成绩
PRIMARY KEY (stuId,subId)
)
GO
--表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87);
GO


1.查询 每门课的 第二名 获得者记录
select * from StudentGrade t
where (select count(*) from StudentGrade where subid=t.subid and grade>t.grade)=1
order by subId,grade DESC;

2.取每位学生的 前2组成绩
Select * From StudentGrade A
Where (Select Count(*) From StudentGrade  Where stuid= A.stuid And grade> A.grade) < 2
Order By stuid , grade desc;

SELECT t1.stuId,t1.subId,grade
FROM StudentGrade t1
WHERE
grade IN (SELECT TOP 2 grade FROM StudentGrade WHERE t1.stuId= stuId ORDER BY grade DESC)
ORDER BY t1.stuid;