表结构：id name subject score createdate
建表语句：
Code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Score](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) CONSTRAINT [DF_Score_name] DEFAULT (''),
[subject] [nvarchar](50) CONSTRAINT [DF_Score_subject] DEFAULT (''),
[score] [float] NOT NULL CONSTRAINT [DF_Score_score] DEFAULT ((0)),
[createdate] [datetime] NOT NULL
) ON [PRIMARY]
向表预赛几条数据：
Code
insert into Score
select '1','Ivan.Mao','English','40','2/25/2009 8:13:42.000'
union
select '2','name8','English','80','2/25/2009 8:13:42.000'
union
select '3','Ivan.Mao','Math','80','2/22/2009 8:13:42.000'
union
select '4','Ivan.Mao','YuWen','58','2/22/2009 12:00:00.000'
union
select '5','name8','Math','80','2/21/2009 11:59:59.000'
union
select '7','Ivan.Mao','DiLi','33','2/21/2009 11:59:59.000'
union
select '8','name1','subject1','80','2/21/2009 11:59:59.000'
union
select '9','name2','subject1','80','2/21/2009 11:59:59.000'
union
select '10','name3','subject1','80','2/21/2009 11:59:59.000'
union
select '11','name4','subject1','90','2/21/2009 11:59:59.000'
union
select '12','name5','subject1','100','2/21/2009 11:59:59.000'
union
select '13','name6','subject1','50','2/21/2009 11:59:59.000'
union
select '14','name7','subject1','95','2/21/2009 11:59:59.000'
1. 创建时间为3天前0点创建的纪录，20分钟前创建的纪录?
select * from Score
where DATEDIFF(d,createdate,GETDATE())>3
select * from Score
where DATEDIFF(n,createdate,GETDATE())>20
2. 3门以上不及格学生的学生姓名?
select [name] from Score
where score<60
group by [name]
having COUNT(*)>=3
3. id name
1 a
2 b
3 a
4 a
id为identity,只留一条a与一条b
--select * into Score_Test3 from Score
select * from Score
select * from Score_Test3
delete from Score_Test3
where id not in
(
select max(id) from Score_Test3 group by [name]
)
4. 总分排名5-7的学生姓名(name,score),最好写成存储过程,请注意并列排名的问题
--select * into Score_Test4 from Score
select * from Score
select * from Score_Test4
select top 3 t2.*
from
(select top 7 [name],sum(score) as totalScore from Score_Test4 group by [name] order by sum(score)) t2
order by t2.totalScore
没有考虑并列排名问题，还待斟酌。。。[/color][/size]
相关资源：敏捷开发V1.0.pptx
