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

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&