统计学生成绩部分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

所有数据：

最终查询结果：