一个简单的sql问题,在线等待

[zww80216]

字段
degree
------
34,55,66,77,88
55,66,77,88,99
66,55,88,44.88
我想分别截取第一个，第二个...,然后分别对它们进行排序

[pengdali]

create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1
select @str_return = substring(@str,@start,@location -@start)
if (@index <> @next ) select @str_return = ''
return @str_return
end
go


--调用：
select dbo.getstrofindex(degree,1),dbo.getstrofindex(degree,2) from 你的表 order by dbo.getstrofindex(degree,1),dbo.getstrofindex(degree,2)

[伍子V5]

其实不是很明白你的意思
select degree from tablename order by left(degree,1)
select degree from tablename order by right(degree,1)

[sdhdy]

create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
-执行
select * from dbo.f_splitstr('1,2,3,4',',')

[txlicenhe]

http://expert.csdn.net/Expert/topic/2298/2298541.xml?temp=.8383905

[txlicenhe]

http://expert.csdn.net/Expert/topic/2298/2298541.xml?temp=.8383905

[zjcxc]

不知道你的截取是否是这个意思?

--创建数据测试环境
declare @tb table(degree varchar(8000))
insert into @tb
select '34,55,66,77,88'
union all select '55,66,77,88,99'
union all select '66,55,88,44.88'

--创建数据处理临时表
select top 8000 id=identity(int,1,1) into #tb
from(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) b,
(select top 100 id from syscolumns) c

--处理数据
select * from(
select degree=cast(substring(degree,b.id,charindex(',',degree+',',b.id)-b.id) as decimal(38,8))
from @tb a,#tb b
where substring(','+degree,b.id,1)=','
) a order by degree

--删除临时表
drop table #tb

[zjcxc]

上面的是分拆成一列,或者是这个,按,分拆成几列:

--创建数据测试环境
declare @tb table(degree varchar(8000))
insert into @tb
select '34,55,66,77,88'
union all select '55,66,77,88,99'
union all select '66,55,88,44.88'

--创建数据处理临时表
select top 8000 id=identity(int,1,1) into #tb
from(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) b,
(select top 100 id from syscolumns) c

declare @sql varchar(8000),@i int

--准备数据处理的临时表
select id=identity(int,1,1),degree into #tb1 from @tb

select id=identity(int,1,1),cast(a.id as int) as oldid
,cast(substring(degree,b.id,charindex(',',degree+',',b.id)-b.id) as decimal(38,8))as newname
into #temp from #tb1 a,#tb b
where substring(','+degree,b.id,1)=','

--生成数据处理的SQL语句
select @sql='',@i=aa
from(select top 1 oldid,count(id) as aa
from #temp group by oldid order by count(id) desc) a

while @i>0
select @sql=char(13)+',max(case a.id-b.id when '+cast(@i-1 as varchar)
+' then a.newname end) as ['+cast(@i as varchar)
+']'+@sql
,@i=@i-1

set @sql='select a.oldid'+char(13)+@sql
+char(13)+' from #temp a'
+char(13)+' ,(select oldid,min(id) as id from #temp group by oldid) b'
+char(13)+'where a.oldid=b.oldid'
+char(13)+'group by a.oldid'

exec(@sql)

--删除临时表
drop table #temp,#tb,#tb1