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 tablenamewhere 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 ta.[objid] a 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 set @str=substring(@str,1,len(@str)-1)
select tab1.a ,IDENTITY(int,1,1) xh into #temp1
from cwf.dbo.fn_split(@str,',') tab1 select @id1=a from #temp1 where xh=1
select @id2=a from #temp1 where xh=2
select @id3=a from #temp1 where xh=3
select @id4=a from #temp1 where xh=4
===========================
拼接字符串。
declare @str varchar(200)
set @str=''
select @str=@str+cast(tb.[objid] as varchar(10))+',' from(
select [objid],sfrq from tablename ) tb order by tb.sfrq
print @str 分割字符串
create FUNCTION [dbo].[fn_split] ( @inputstr varchar(8000), @seprator varchar(10) )
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i +1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> ''
insert @temp values(@inputstr) return
end