SQL如何编辑这样的函数呢？？？

[ni_123_mi]

表table1中有这样一个字段NO
10，20，30
20，30，50
10，30
30，50
30

值个数不确定，但是值里的数值是10、20、30、50组合的

有这样的一个对应关系
10——>200
20——>290
30——>200
50——>290
现在需要找出字段NO中值的最小值，并把对应的值输出
例如：10，20，30 最小值是10，输出200

如何写个函数实现这种功能呢？
急用，请各位大师指教！

[dawugui]

create table tb(id int, NO2 varchar(50))
insert into tb values(1,'70，120，70，150，95')
insert into tb values(2,'70，120，95')
insert into tb values(3,'120，95，150')
insert into tb values(4,'70，95')
insert into tb values(5,'95，150')
insert into tb values(6,'95')
create table glb(no2 int, show int)
insert into glb values(70 ,200)
insert into glb values(120,290)
insert into glb values(95 ,200)
insert into glb values(150,290)
go

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

select m.* , n.show from 
(
select id , min(no2) no2 from
(
SELECT A.id, NO2 = SUBSTRING(A.[NO2], B.id, CHARINDEX('，', A.[NO2] + '，', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING('，' + A.[NO2], B.id, 1) = '，'
) t group by id
) m , glb n
where m.no2 = n.no2
order by id

DROP TABLE #

drop table tb , glb

/*
id          no2                                                show        
----------- -------------------------------------------------- ----------- 
1           120                                                290
2           120                                                290
3           120                                                290
4           70                                                 200
5           150                                                290
6           95                                                 200

（所影响的行数为 6 行）
*/

[dawugui]

先拆分,然后分组取min()

/*
标题：分拆列值1
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述

有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/

--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','

DROP TABLE #

--2. 新的解决方法(sql server 2005) 
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B

DROP TABLE tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/

[水族杰纶]

--> Title  : Generating test data [tb]
--> Author : wufeng4552
--> Date   : 2009-11-19 11:17:23
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] ([NO] varchar(20))
insert into [tb]
select '10,20,30' union all
select '20,30,50' union all
select '10,30' union all
select '30,50' union all
select '30'

if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
  begin
  return(
    select col=min(substring(@s,number,charindex(',',@s+',',number)-number))
    from master..spt_values
    where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
  end
go
select case when dbo.UF_minget([No])=10 then 200
            when dbo.UF_minget([No])=20 then 290  
            when dbo.UF_minget([No])=30 then 200
            when dbo.UF_minget([No])=50 then 290
       end
from tb
/*

-----------
200
290
200
200
200

(5 個資料列受到影響)

*/

[水族杰纶]

--更多方法 參考
MSSQL多列取最大或者最小值
http://blog.csdn.net/wufeng4552/archive/2009/10/16/4681510.aspx

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-19 11:20:49
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[value] varchar(8))
insert [tb]
select 1,'10,20,30' union all
select 2,'20,30,50' union all
select 3,'10,30' union all
select 4,'30,50' union all
select 5,'30'
--------------开始查询--------------------------
Select
    a.id,[value]=substring(a.[value],b.number,charindex(',',a.[value]+',',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[value])
where
     substring(','+a.[value],b.number,1)=','
----------------结果----------------------------
/* 
(5 行受影响)
id          value
----------- --------
1           10
1           20
1           30
2           20
2           30
2           50
3           10
3           30
4           30
4           50
5           30

(11 行受影响)


*/

[icelovey]

declare @tb table([id] int,[values] varchar(8))
insert @tb
select 1,'10,20,30' union all
select 2,'20,30,50' union all
select 3,'10,30' union all
select 4,'30,50' union all
select 5,'30'

declare @tb1 table([f1] int,[f2] int)
insert @tb1
select 10,200 union all
select 20,290 union all
select 30,200 union all
select 50,290

SELECT TOP 100 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT T1.ID, T2.[f2]
FROM (
			SELECT ID, MIN(NUM) AS NUM
			FROM (
					SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)  AS NUM
					FROM @tb A, # B 
					WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' 
				 ) P
			GROUP BY ID
	) AS T1 INNER JOIN @tb1 T2 ON T1.NUM = T2.[f1]

DROP TABLE # 

/*
(100 row(s) affected)
ID          f2
----------- -----------
1           200
2           290
3           200
4           200
5           200

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-19 11:20:49
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[value] varchar(8))
insert [tb]
select 1,'10,20,30' union all
select 2,'20,30,50' union all
select 3,'10,30' union all
select 4,'30,50' union all
select 5,'30'
--------------开始查询--------------------------
;with f as
(
Select
    a.id,[value]=substring(a.[value],b.number,charindex(',',a.[value]+',',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[value])
where
     substring(','+a.[value],b.number,1)=','
)
select id,min(value) from f group by id
----------------结果----------------------------
/* 
id          
----------- --------
1           10
2           20
3           10
4           30
5           30

(5 行受影响)

*/

[icelovey]

declare @tb table([id] int,[values] varchar(8))
insert @tb
select 1,'10,20,30' union all
select 2,'20,30,50' union all
select 3,'10,30' union all
select 4,'30,50' union all
select 5,'30'

declare @tb1 table([f1] int,[f2] int)
insert @tb1
select 10,200 union all
select 20,290 union all
select 30,200 union all
select 50,290

SELECT TOP 100 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT T1.ID, T1.[values], T2.[f2]
FROM (
			SELECT ID, [values], MIN(NUM) AS NUM
			FROM (
					SELECT A.id, A.[values], SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)  AS NUM
					FROM @tb A, # B 
					WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' 
				 ) P
			GROUP BY ID, [values]
	) AS T1 INNER JOIN @tb1 T2 ON T1.NUM = T2.[f1]

DROP TABLE # 

/*
ID          values   f2
----------- -------- -----------
1           10,20,30 200
3           10,30    200
2           20,30,50 290
5           30       200
4           30,50    200

(5 row(s) affected)

[ni_123_mi]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] ([NO] varchar(20))
insert into [tb]
select '70,95,120' union all
select '120,95,150' union all
select '70,95' union all
select '95,150' union all
select '150'

if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
  begin
  return(
    select col=min(substring(@s,number,charindex(',',@s+',',number)-number))
    from master..spt_values
    where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
  end
go
select case when dbo.UF_minget([No])=70 then 200
            when dbo.UF_minget([No])=120 then 290  
            when dbo.UF_minget([No])=95 then 200
            when dbo.UF_minget([No])=150 then 290
       end
from tb


/*
290
290
200
290
290
*/

我改了一下数值，为什么结果就不正确了呢？大师帮忙看一下