统计学生成绩一部分sql语句

统计学生成绩部分sql语句

1，建表语句

CREATE TABLE [dbo].[Grades](
	[student] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[grade] [int] NULL
) ON [PRIMARY]

2，插入数据

insert grades (student,subject,grade)  values ( 'student1','subject1',89)
insert grades (student,subject,grade)  values ( 'student1','subject2',32)
insert grades (student,subject,grade)  values ( 'student1','subject3',79)
insert grades (student,subject,grade)  values ( 'student2','subject1',70)
insert grades (student,subject,grade)  values ( 'student2','subject2',59)
insert grades (student,subject,grade)  values ( 'student2','subject3',84)
insert grades (student,subject,grade)  values ( 'student3','subject1',67)
insert grades (student,subject,grade)  values ( 'student3','subject2',48)
insert grades (student,subject,grade)  values ( 'student3','subject3',79)

3，sql

-- 查询每个学生的最高分科目
select * from grades a where grade in
(
	select max(grade) from grades B where a.Student=B.Student
)
order by student

-- 查询单科最高分
select * from grades a where grade in
(
	select max(grade) from grades B where a.Subject=B.Subject
)
order by Subject

 

所有数据：

最终查询结果：