一道sql试题——据说是盛大的

表结构：id   name   subject   score   createdate

建表语句：

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]

向表预赛几条数据：

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

 

没有考虑并列排名问题，还待斟酌。。。