|
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) www.2cto.com 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())) www.2cto.com 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语句： [sql] select distinct si.id,si.username,si.score,si.classID,ci.className from stuinfo as si inner join ( www.2cto.com 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 原始数据： 
查询结果：
 |