统计学生成绩部分sql语句

返回顶部
查看留言
转到底部

现在的位置: 首页 > 综合 > 正文

统计学生成绩部分sql语句

2013年11月29日 ⁄ 综合 ⁄ 共 1007字 ⁄ 字号小中大 ⁄ 评论关闭

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

所有数据：