--创建一个自定义函数,实现分解 create function f_split( @s varchar(8000), @pos int )returns varchar(8000) as begin declare @i int set @i=charindex(',',@s) while @i>0 and @pos>1 select @s=substring(@s,@i+1,8000) ,@i=charindex(',',@s) ,@pos=@pos-1 return(case @pos when 1 then case when @i>0 then left(@s,@i-1) else @s end else '' end) end go --调用这个分拆函数实现楼主要的取数功能 declare @a varchar(100) set @a='1,3,45,6,7,2,9' select dbo.f_split(@a,(len(@a)-len(replace(@a,',','')))/2+1)set @a='1,2,3,4,5' select dbo.f_split(@a,(len(@a)-len(replace(@a,',','')))/2+1)godrop function f_split--结果: -------------------- 6(所影响的行数为 1 行) --------------------- 3(所影响的行数为 1 行)
declare @t table(A1 varchar(10),A2 int) insert @t select 'a',1 union all select 'b',5 union all select 'c',20 union all select 'd',7 union all select 'f',4--查询 select * from @t a where( select count(*) from @t where a2<=a.a2 )=(select count(*) from @t)/2+1--结果:A1 A2 ---------- ----------- b 5(所影响的行数为 1 行)
create function f_split(
@s varchar(8000),
@pos int
)returns varchar(8000)
as
begin
declare @i int set @i=charindex(',',@s)
while @i>0 and @pos>1
select @s=substring(@s,@i+1,8000)
,@i=charindex(',',@s)
,@pos=@pos-1
return(case @pos when 1
then case when @i>0 then left(@s,@i-1) else @s end
else '' end)
end
go
--调用这个分拆函数实现楼主要的取数功能
declare @a varchar(100)
set @a='1,3,45,6,7,2,9'
select dbo.f_split(@a,(len(@a)-len(replace(@a,',','')))/2+1)set @a='1,2,3,4,5'
select dbo.f_split(@a,(len(@a)-len(replace(@a,',','')))/2+1)godrop function f_split--结果:
--------------------
6(所影响的行数为 1 行)
---------------------
3(所影响的行数为 1 行)
insert @t select 'a',1
union all select 'b',5
union all select 'c',20
union all select 'd',7
union all select 'f',4--查询
select * from @t a
where(
select count(*) from @t where a2<=a.a2
)=(select count(*) from @t)/2+1--结果:A1 A2
---------- -----------
b 5(所影响的行数为 1 行)
好像还有错误,当行中的数字有重复,
行数为奇数时,有时出不来
偶数时出两行