真爱无限的知识驿站

数据库表和数据准备：

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)