34,838
社区成员




convert(char(10),dateadd(day,-3,getdate()),120)
dateadd(minute,-20,getdate())
--3天前0点以前创建的记录
select * from test
where convert(char(10),createtime,120)<convert(char(10),dateadd(day,-3,getdate()),120)
--20分钟前创建的记录
select * from test
where createtime<dateadd(minute,-20,getdate())
--查询有3门功课不及格的学生
select [name] from test
where score<60
group by [name]
having count(*)>=3
--将及格的成绩更新为1,不及格的更新为0
update test
set score=(case when score>=60 then 1 else 0 end)
/*
5.删除重复姓名的记录。如
1 A
2 B
3 A
4 A
删除后只剩A和B两条记录。
*/
declare @t table([id] int,[name] varchar(2))
insert @t select 1,'A'
union all select 2,'B'
union all select 3,'A'
union all select 4,'A'
delete @t
where [id] not in(
select [id] from @t t
where [id]=(select min([id]) from @t where [name]=t.[name])
)
select * from @t
/*
id name
----------- ----
1 A
2 B
*/