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

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

原始数据:

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

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

查询结果:

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

分类:默认分类 时间:2015-03-09 人气:1
本文关键词:
分享到:

相关文章

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.056 (s). 10 q(s)