一道sql试题——据说是盛大的-优快云博客

本文通过具体的SQL实战案例，展示了如何利用SQL进行数据筛选、分组、聚合等操作，包括查询特定条件的数据记录、找出多门课程不及格的学生名单、处理重复数据及实现复杂排名等功能。

表结构：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

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

转载于:https://www.cnblogs.com/MaoBisheng/archive/2009/02/25/1397664.html