显示 包括选择的这条,在加上 选择年度的此人 最近的 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