函数 datediff(根据objid 获取同name 同年度最近的4条记录)

显示  包括选择的这条，在加上 选择年度的此人 最近的 3条。（最多显示4条）
1.@count>4   记录数大于4条
2.@count<4   全显示

create table temp(
objid int identity(1,1) primary key ,
name varchar(20) ,
rq datetime default(getdate())
)
select * from temp


--根据objid 获取同name 同年度最近的4条记录，含该条。

declare @objid int 
set @objid=1
exec pro_test @objidselect a.* from temp as a 
join (select name,year(rq) as rq from temp where objid=1) as b 
on a.name=b.name and year(a.rq)=b.rq 
union 
--objid最近的条数据

select top 4 * from temp as a
join (select * from temp where objid=1) as b 
on a.rq<b.rq
order by a.rq desc

insert into temp(name,rq) values ('a','2013-3-3')

insert into temp(name,rq) values ('a','2013-3-9')
insert into temp(name,rq) values ('a','2013-4-3')
insert into temp(name,rq) values ('a','2013-5-3')
insert into temp(name,rq) values ('a','2013-6-3')
insert into temp(name,rq) values ('a','2013-9-3')
insert into temp(name,rq) values ('a','2013-9-9')
insert into temp(name,rq) values ('a','2013-10-3')
insert into temp(name,rq) values ('a','2013-10-11')
insert into temp(name,rq) values ('a','2013-11-3')
insert into temp(name,rq) values ('a','2013-11-13')
insert into temp(name,rq) values ('a','2013-12-3')
insert into temp(name,rq) values ('a','2013-12-13')

insert into temp(name,rq) values ('b','2013-3-3')

insert into temp(name,rq) values ('b','2013-3-9')
insert into temp(name,rq) values ('b','2013-4-3')
insert into temp(name,rq) values ('b','2013-5-3')
insert into temp(name,rq) values ('b','2013-6-3')
insert into temp(name,rq) values ('b','2013-9-3')
insert into temp(name,rq) values ('b','2013-9-9')
insert into temp(name,rq) values ('b','2013-10-3')
insert into temp(name,rq) values ('b','2013-10-11')
insert into temp(name,rq) values ('b','2013-11-3')
insert into temp(name,rq) values ('b','2013-11-13')
insert into temp(name,rq) values ('b','2013-12-3')
insert into temp(name,rq) values ('b','2013-12-13')

insert into temp(name,rq) values ('c','2013-3-3')

insert into temp(name,rq) values ('c','2013-3-9')
insert into temp(name,rq) values ('c','2013-4-3')
insert into temp(name,rq) values ('c','2013-5-3')
insert into temp(name,rq) values ('c','2013-6-3')
insert into temp(name,rq) values ('c','2013-9-3')
insert into temp(name,rq) values ('c','2013-9-9')
insert into temp(name,rq) values ('c','2013-10-3')
insert into temp(name,rq) values ('c','2013-10-11')
insert into temp(name,rq) values ('c','2013-11-3')
insert into temp(name,rq) values ('c','2013-11-13')
insert into temp(name,rq) values ('c','2013-12-3')
insert into temp(name,rq) values ('c','2013-12-13')


declare @objid int 
set @objid=9
declare @name varchar(20),@rq datetime ,@count int,@str varchar(200)
select @name = name,@rq =rq from temp where [objid]=@objid
select @count=COUNT(*) from temp 
where name=@name and YEAR (rq)= YEAR(@rq)

print @count
if @count>4 
begin

select top 4 * from  temp 
where name=@name  and YEAR (rq)= YEAR(@rq)
order by abs(datediff(day,sfrq,@sfrq)) asc 
end
else 
begin 
select * from  temp where name=@name and YEAR (rq)= YEAR(@rq)
end

比较时间类型
使用datediff()
datediff(day,sfrq,@sfrq)  返回两时间日期差值。
=========================================================================================================

declare @id1 int,@id2 int,@id3 int,@id4 int
declare @sickcode varchar(20),@sfrq datetime ,@count int,@str varchar(200)
select @sickcode = sickcode,@sfrq =sfrq from tablename where objid=@objid
select @count=COUNT(*) from tablename
where sickcode=@sickcode and YEAR (sfrq)= YEAR(@sfrq)

if @count>4 
begin
select  @str=@str+cast(ta.[objid] as varchar(10))+',' from(
select top 4 [objid] ,sfrq from tablename
where sickcode=@sickcode  and YEAR (sfrq)= YEAR(@sfrq)
order by abs(datediff(day,sfrq,@sfrq)) asc ) ta order by ta.sfrq
end
else 
begin 
select  @str=@str+cast(tb.[objid] as varchar(10))+',' from(
select [objid],sfrq  from  tablename where sickcode=@sickcode and YEAR (sfrq)= YEAR(@sfrq)
 ) tb order by tb.sfrq
end