mysql求每个班级的最高分_sql查询每个班上成绩最高的学生信息

最新推荐文章于 2024-07-03 20:52:15 发布

原创最新推荐文章于 2024-07-03 20:52:15 发布 · 4.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql求每个班级的最高分

本文提供了一种SQL查询方法，用于从数据库中找出每个班级得分最高的学生信息。通过创建并填充"classinfo"和"stuinfo"表，展示了如何利用内连接和聚合函数MAX来获取每个班级的最高分，并显示对应学生的详细信息。

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

sql查询每个班上成绩最高的学生信息

数据库表和数据准备：

if exists (select * from sysobjects where id = OBJECT_ID('[classinfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

DROP TABLE [classinfo]

CREATE TABLE [classinfo] (

[id] [bigint] NOT NULL,

[classID] [bigint] NOT NULL,

[className] [nvarchar] (50) NOT NULL,

[stat] [varchar] (2) NOT NULL DEFAULT (1),

[autoid] [bigint] IDENTITY (1, 1) NOT NULL)

alter TABLE [classinfo] WITH NOCHECK ADD CONSTRAINT [PK_classinfo] PRIMARY KEY NONCLUSTERED ( [id] )

SET IDENTITY_INSERT [classinfo] ON

INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 1,1,N'计算机一班',N'1',1)

INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 2,2,N'计算机二班',N'1',2)

INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 4,3,N'计算机三班',N'0',3)

INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 5,4,N'计算机四班',N'1',4)

INSERT [classinfo] ([id],[classID],[className],[stat],[autoid]) VALUES ( 6,5,N'计算机五班',N'1',6)

SET IDENTITY_INSERT [classinfo] OFF

if exists (select * from sysobjects where id = OBJECT_ID('[stuinfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

DROP TABLE [stuinfo]

CREATE TABLE [stuinfo] (

[id] [bigint] IDENTITY (1, 1) NOT NULL,

[username] [nvarchar] (50) NULL,

[userpwd] [nvarchar] (50) NULL,

[classID] [bigint] NULL,

[score] [numeric] (10,2) NULL,

[age] [int] NULL,

[CreateTime] [datetime] NULL DEFAULT (getdate()))

alter TABLE [stuinfo] WITH NOCHECK ADD CONSTRAINT [PK_stuinfo] PRIMARY KEY NONCLUSTERED ( [id] )

SET IDENTITY_INSERT [stuinfo] ON

INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 1,N'001',N'0004',1,123.22,25,N'2012/8/24 10:58:10')

INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 2,N'002',N'154',2,888.00,21,N'2012/8/24 10:58:10')

INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 3,N'003',N'555',1,888.00,16,N'2012/8/24 10:58:10')

INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 4,N'004',N'644',2,85.60,18,N'2012/8/24 10:58:10')

INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 5,N'pkm',N'123',3,46.00,19,N'2012/8/24 10:58:10')

INSERT [stuinfo] ([id],[username],[userpwd],[classID],[score],[age],[CreateTime]) VALUES ( 6,N'pkm001',N'123',3,45.56,19,N'2012/8/24 10:58:10')

INSERT [stuinfo] ([id],[username],[classID],[CreateTime]) VALUES ( 7,N'2012pkm1',1,N'2012/8/24 10:58:10')

SET IDENTITY_INSERT [stuinfo] OFF

查询每班最高分数的sql语句：

select distinct si.id,si.username,si.score,si.classID,ci.className

from stuinfo as si

inner join

(

select max(score) as score ,classID from stuinfo

group by classID

)

as c1

on c1.score = si.score

inner join classinfo as ci

on ci.classID = si.classID

order by si.classID

原始数据：

查询结果：

附：删除重复username记录，只保留最小的id

delete from stuinfo

where id not in

(

select si.id

from stuinfo as si

inner join

(

select min(id) as id ,username from stuinfo

group by username

)

as c1

on c1.id = si.id

)

---或者

delete from stuinfo where id not in

(select min(id) from stuinfo group by username)