mysql求每个班级的最高分_SQL 如何查找一个表里，每个班级各个学科的最高分？...

最新推荐文章于 2024-07-22 03:03:30 发布

weixin_39665379

最新推荐文章于 2024-07-22 03:03:30 发布

阅读量993

点赞数

CC 4.0 BY-SA版权

文章标签： mysql求每个班级的最高分

本文链接：https://blog.csdn.net/weixin_39665379/article/details/113476466

这篇博客展示了如何使用SQL查询每个班级在不同科目上的最高分。通过创建和插入数据到CLASS, STUDENT, TEACHER, COURSE和SC表中，然后执行SQL查询来找出每个班级每个科目的最高分。" 51526583,643628,使用双网卡在Windows 7中提升多任务下载速度,"['windows', '网络配置', '下载优化']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

```sql

-- Create table

create table CLASS

(

classno INTEGER not null,

classname NVARCHAR2(32)

);

-- Add comments to the columns

comment on column CLASS.classno

is '班级编号';

comment on column CLASS.classname

is '班级名称';

-- Create/Recreate primary, unique and foreign key constraints

alter table CLASS

add constraint PK_CLASS primary key (CLASSNO);

-- Create table

create table STUDENT

(

s# INTEGER not null,

sname NVARCHAR2(32),

sage INTEGER,

ssex NVARCHAR2(8),

classno INTEGER

);

-- Add comments to the columns

comment on column STUDENT.s#

is '学号';

comment on column STUDENT.sname

is '姓名';

comment on column STUDENT.sage

is '年龄';

comment on column STUDENT.ssex

is '性别';

comment on column STUDENT.classno

is '班级编号';

-- Create/Recreate primary, unique and foreign key constraints

alter table STUDENT

add constraint PK_STUDENT primary key (S#);

alter table STUDENT

add constraint FK_STUDENT_CLASS foreign key (CLASSNO)

references CLASS (CLASSNO);

-- Create table

create table TEACHER

(

t# INTEGER not null,

tname NVARCHAR2(16)

);

-- Add comments to the columns

comment on column TEACHER.t#

is '教师编号';

comment on column TEACHER.tname

is '教师姓名';

-- Create/Recreate primary, unique and foreign key constraints

alter table TEACHER

add constraint PK_TEACHER primary key (T#);

-- Create table

create table COURSE

(

c# INTEGER not null,

cname NVARCHAR2(32),

t# INTEGER

);

-- Add comments to the columns

comment on column COURSE.c#

is '课程号';

comment on column COURSE.cname

is '课程名称';

comment on column COURSE.t#

is '教师编号';

-- Create/Recreate primary, unique and foreign key constraints

alter table COURSE

add constraint PK_COURSE primary key (C#);

alter table COURSE

add constraint FK_COURSE_TEACHER foreign key (T#)

references TEACHER (T#);

-- Create table

create table SC

(

s# INTEGER,

c# INTEGER,

score INTEGER

);

-- Add comments to the columns

comment on column SC.s#

is '学号';

comment on column SC.c#

is '课程号';

comment on column SC.score

is '分数';

-- Create/Recreate primary, unique and foreign key constraints

alter table SC

add constraint FK_SC_COURSE foreign key (C#)

references COURSE (C#);

alter table SC

add constraint FK_SC_STUDENT foreign key (S#)

references STUDENT (S#);

INSERT INTO CLASS VALUES ('2019001', '火箭一班');

INSERT INTO CLASS VALUES ('2019002', '火箭二班');

INSERT INTO STUDENT VALUES ('1001', '曹操', '21', '男', '2019001');

INSERT INTO STUDENT VALUES ('1002', '貂蝉', '18', '女', '2019001');

INSERT INTO STUDENT VALUES ('1003', '袁绍', '22', '男', '2019001');

INSERT INTO STUDENT VALUES ('1004', '刘备', '23', '男', '2019002');

INSERT INTO STUDENT VALUES ('1005', '小乔', '20', '女', '2019002');

INSERT INTO STUDENT VALUES ('1006', '关羽', '21', '男', '2019002');

INSERT INTO TEACHER VALUES ('801', '叶平');

INSERT INTO TEACHER VALUES ('802', '李华');

INSERT INTO TEACHER VALUES ('803', '刘明');

INSERT INTO TEACHER VALUES ('804', '李斯');

INSERT INTO COURSE VALUES ('1', '计算机组成原理', '801');

INSERT INTO COURSE VALUES ('2', '数据结构', '801');

INSERT INTO COURSE VALUES ('3', '数据库原理及应用', '802');

INSERT INTO COURSE VALUES ('4', '计算机网络', '802');

INSERT INTO COURSE VALUES ('5', '软件工程', '803');

INSERT INTO COURSE VALUES ('6', 'C语言程序设计', '803');

INSERT INTO COURSE VALUES ('7', '人工智能', '804');

INSERT INTO COURSE VALUES ('8', '操作系统', '804');

INSERT INTO SC VALUES ('1001', '1', '61');

INSERT INTO SC VALUES ('1001', '2', '67');

INSERT INTO SC VALUES ('1001', '3', '91');

INSERT INTO SC VALUES ('1001', '4', '66');

INSERT INTO SC VALUES ('1001', '5', '95');

INSERT INTO SC VALUES ('1001', '6', '68');

INSERT INTO SC VALUES ('1001', '7', '73');

INSERT INTO SC VALUES ('1001', '8', '99');

INSERT INTO SC VALUES ('1002', '1', '94');

INSERT INTO SC VALUES ('1002', '2', '85');

INSERT INTO SC VALUES ('1002', '3', '69');

INSERT INTO SC VALUES ('1002', '4', '78');

INSERT INTO SC VALUES ('1002', '5', '70');

INSERT INTO SC VALUES ('1002', '6', '77');

INSERT INTO SC VALUES ('1002', '7', '83');

INSERT INTO SC VALUES ('1002', '8', '81');

INSERT INTO SC VALUES ('1003', '1', '77');

INSERT INTO SC VALUES ('1003', '2', '70');

INSERT INTO SC VALUES ('1003', '3', '97');

INSERT INTO SC VALUES ('1003', '4', '92');

INSERT INTO SC VALUES ('1003', '5', '65');

INSERT INTO SC VALUES ('1003', '6', '89');

INSERT INTO SC VALUES ('1003', '7', '79');

INSERT INTO SC VALUES ('1003', '8', '66');

INSERT INTO SC VALUES ('1004', '1', '70');

INSERT INTO SC VALUES ('1004', '2', '95');

INSERT INTO SC VALUES ('1004', '3', '78');

INSERT INTO SC VALUES ('1004', '4', '68');

INSERT INTO SC VALUES ('1004', '5', '72');

INSERT INTO SC VALUES ('1004', '6', '98');

INSERT INTO SC VALUES ('1004', '7', '81');

INSERT INTO SC VALUES ('1004', '8', '84');

INSERT INTO SC VALUES ('1005', '1', '93');

INSERT INTO SC VALUES ('1005', '2', '96');

INSERT INTO SC VALUES ('1005', '3', '73');

INSERT INTO SC VALUES ('1005', '4', '76');

INSERT INTO SC VALUES ('1005', '5', '85');

INSERT INTO SC VALUES ('1005', '6', '90');

INSERT INTO SC VALUES ('1005', '7', '77');

INSERT INTO SC VALUES ('1005', '8', '62');

INSERT INTO SC VALUES ('1006', '1', '84');

INSERT INTO SC VALUES ('1006', '2', '86');

INSERT INTO SC VALUES ('1006', '3', '63');

INSERT INTO SC VALUES ('1006', '4', '66');

INSERT INTO SC VALUES ('1006', '5', '60');

INSERT INTO SC VALUES ('1006', '6', '87');

INSERT INTO SC VALUES ('1006', '7', '65');

INSERT INTO SC VALUES ('1006', '8', '99');