统计学生成绩部分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
所有数据：
最终查询结果：
