SQL自定义排序函数

[p094611256]

假如有六个数 3.2，2.3，1.3，5.5，6.6，7.8
我需要一个SQL自定义的函数，使得输出结果为：
1.3，2.3，3.2，5.5，6.6，7.8

[billpu]

if object_id('tb')>0
    drop table tb
create table tb
(
num varchar(100)
)

insert into tb
select '3.2,2.3,1.3,5.5,6.6,7.8'


CREATE FUNCTION  dbo.f_str()
RETURNS VARCHAR(1000)
AS
BEGIN
declare @s varchar(1000)
select @s =num from tb
DECLARE @t table(col VARCHAR(100))
WHILE CHARINDEX(',',@s)>0
BEGIN
INSERT INTO @t SELECT  LEFT(@s,CHARINDEX(',',@s)-1)
SET @s=STUFF(@s,1,CHARINDEX(',',@s),'')
END
insert into @t SELECT @s
SET @s=''
select @s =@s+cast(col as varchar(20)) +',' from @t order by col
RETURN left(@s,len(@s)-1)
END

GO
--调用
SELECT dbo.f_str()
--result
--1.3,2.3,3.2,5.5,6.6,7.8

[billpu]

多记录的时候有个bug,加个参数

if object_id('tb')>0
    drop table tb
create table tb
(
num varchar(100)
)

insert into tb
select '3.2,2.3,1.3,5.5,6.6,7.8' UNION ALL
SELECT '0.1,3.2,2.3,1.3,5.5,6.6,7.8'

GO

CREATE FUNCTION  dbo.f_str(@str VARCHAR(100))
RETURNS VARCHAR(1000)
AS
BEGIN
declare @s varchar(1000)
select @s =num from tb WHERE num=@str
DECLARE @t table(col VARCHAR(100))
WHILE CHARINDEX(',',@s)>0
BEGIN
INSERT INTO @t SELECT  LEFT(@s,CHARINDEX(',',@s)-1)
SET @s=STUFF(@s,1,CHARINDEX(',',@s),'')
END
insert into @t SELECT @s
SET @s=''
select @s =@s+cast(col as varchar(20)) +',' from @t order by col
RETURN LEFT(@s,LEN(@s)-1)
END

GO
--调用
SELECT dbo.f_str(num) FROM tb
--result
--1.3,2.3,3.2,5.5,6.6,7.8
--0.1,1.3,2.3,3.2,5.5,6.6,7.8

[hao1hao2hao3]

if object_id('tb')>0
	drop table tb
create table tb
(
	num numeric(10,2)
)

insert into tb
select 3.2
union all
select 2.3
union all
select 1.3
union all
select 5.5
union all
select 6.6
union all
select 7.8

declare @s varchar(max)
set @s =''
select @s =@s+ cast(num as varchar(20)) +',' from tb 
order by num
print left(@s,len(@s)-1 )


结果

1.30,2.30,3.20,5.50,6.60,7.80

[billpu]

帮写成自定义函数 sql2000

if object_id('tb')>0
    drop table tb
create table tb
(
num numeric(10,2)
)

insert into tb
select 3.2
union all
select 2.3
union all
select 1.3
union all
select 5.5
union all
select 6.6
union all
select 7.8
GO

CREATE FUNCTION  dbo.f_str()
RETURNS VARCHAR(1000)
AS
BEGIN
declare @s varchar(1000)
SET @s=''
select @s =@s+ cast(num as varchar(20)) +',' from tb order by num
RETURN left(@s,len(@s)-1)
END
GO
--调用
SELECT dbo.f_str()

--result
1.30,2.30,3.20,5.50,6.60,7.80

[p094611256]

[Quote=引用 2 楼 billpu 的回复:]

帮写成自定义函数 sql2000
SQL code
if object_id('tb')>0
drop table tb
create table tb
(
num numeric(10,2)
)

insert into tb
select 3.2
union all
select 2.3
union all
select 1.3
union all
select 5.5
unio……
[/Quote]

是一行数据，不是六行

[p094611256]

[Quote=引用 1 楼 hao1hao2hao3 的回复:]

SQL code


if object_id('tb')>0
drop table tb
create table tb
(
num numeric(10,2)
)

insert into tb
select 3.2
union all
select 2.3
union all
select 1.3
union all
select 5.5
union all
select……
[/Quote]

你这样是六行数据，我的意思是有六个数值型的参数，对其进行排序