if
not
object_id
(
'
Tempdb..#T
'
)
is
null
drop table #T
Go
Create table #T( [ ID ] int , [ NAME ] nvarchar ( 1 ), [ DTIME ] Datetime )
Insert #T
select 1 ,N ' 张 ' , ' 2007-12-15 ' union all
select 2 ,N ' 刘 ' , ' 2008-10-12 ' union all
select 3 ,N ' 王 ' , ' 2009-10-13 ' union all
select 4 ,N ' 赵 ' , ' 2009-12-15 ' union all
select 5 ,N ' 孙 ' , ' 2009-12-17 ' union all
select 6 ,N ' 于 ' , ' 2009-12-14 ' union all
select 7 ,N ' 李 ' , ' 2009-12-10 ' union all
select 8 ,N ' 高 ' , ' 2009-12-01 ' union all
select 9 ,N ' 金 ' , ' 2009-12-10 '
Go
Select * from #T
select*from[表名]wheredatediff(day,DTIme,getdate())>5and[Name]in (select[Name]from[表名]groupby[Name]havingcount([Name])>2)
declare @i int
select @i = count ( * ) from #T where DateDiff ( day , [ DTIME ] , getdate ()) > 5
if @i > 2
select * from #T where DateDiff ( day , [ DTIME ] , getdate ()) > 5
/*
ID NAME DTIME
----------- ---- -----------------------
1 张 2007-12-15 00:00:00.000
2 刘 2008-10-12 00:00:00.000
3 王 2009-10-13 00:00:00.000
7 李 2009-12-10 00:00:00.000
8 高 2009-12-01 00:00:00.000
9 金 2009-12-10 00:00:00.000
(6 行受影响)
drop table #T
Go
Create table #T( [ ID ] int , [ NAME ] nvarchar ( 1 ), [ DTIME ] Datetime )
Insert #T
select 1 ,N ' 张 ' , ' 2007-12-15 ' union all
select 2 ,N ' 刘 ' , ' 2008-10-12 ' union all
select 3 ,N ' 王 ' , ' 2009-10-13 ' union all
select 4 ,N ' 赵 ' , ' 2009-12-15 ' union all
select 5 ,N ' 孙 ' , ' 2009-12-17 ' union all
select 6 ,N ' 于 ' , ' 2009-12-14 ' union all
select 7 ,N ' 李 ' , ' 2009-12-10 ' union all
select 8 ,N ' 高 ' , ' 2009-12-01 ' union all
select 9 ,N ' 金 ' , ' 2009-12-10 '
Go
Select * from #T
select*from[表名]wheredatediff(day,DTIme,getdate())>5and[Name]in (select[Name]from[表名]groupby[Name]havingcount([Name])>2)
declare @i int
select @i = count ( * ) from #T where DateDiff ( day , [ DTIME ] , getdate ()) > 5
if @i > 2
select * from #T where DateDiff ( day , [ DTIME ] , getdate ()) > 5
/*
ID NAME DTIME
----------- ---- -----------------------
1 张 2007-12-15 00:00:00.000
2 刘 2008-10-12 00:00:00.000
3 王 2009-10-13 00:00:00.000
7 李 2009-12-10 00:00:00.000
8 高 2009-12-01 00:00:00.000
9 金 2009-12-10 00:00:00.000
(6 行受影响)
*/
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638194.html，如需转载请自行联系原作者
