下面每一行都是表table_A中字段colum_A的值，每一个值都是由逗号分隔、多个整数组成的数字串:
1,102,
1,105,
2,205,
1,101,10102,1010202,
2,202,20203,
2,203,20303,
1,104,10402,
1,105,21,
2,206,50008164,50008822,
1,105,50008164,50001705,211509,
1,105,50008164,50001705,50008398,50008400,
现在需要求出表中所有不同的整数并列出来，怎样用一个最简单的存储过程高效率的实现？
------解决方案--------------------
--刚才贴得太急了，这个是正确的
create table #table_A(colum_A varchar(2000))
insert #table_A
SELECT '1,102,' AS colum_A UNION ALL
SELECT '1,105,' AS colum_A UNION ALL
SELECT '2,205,' AS colum_A UNION ALL
SELECT '1,101,10102,1010202,' AS colum_A UNION ALL
SELECT '2,202,20203, ' AS colum_A UNION ALL
SELECT '2,203,20303,' AS colum_A UNION ALL
SELECT '1,104,10402,' AS colum_A UNION ALL
SELECT '1,105,21,' AS colum_A UNION ALL
SELECT '2,206,50008164,50008822,' AS colum_A UNION ALL
SELECT '1,105,50008164,50001705,211509,' AS colum_A UNION ALL
SELECT '1,105,50008164,50001705,50008398,50008400, ' AS colum_A
CREATE TABLE #Temp(colum_A INT)
WHILE EXISTS(SELECT 1 FROM #table_A WHERE colum_A<>'')
BEGIN
INSERT #Temp
SELECT DISTINCT LEFT(colum_A,CHARINDEX(',',colum_A)-1)
FROM #table_A
WHERE CHARINDEX(',',colum_A)>1
AND LEFT(colum_A,CHARINDEX(',',colum_A)-1) NOT IN (SELECT colum_A FROM #Temp)
AND ISNUMERIC(LEFT(colum_A,CHARINDEX(',',colum_A)-1))=1
UPDATE #table_A
SET colum_A=STUFF(colum_A,1,CHARINDEX(',',colum_A),'')
WHERE CHARINDEX(',',colum_A)>1
END
SELECT * FROM #Temp
SELECT * FROM #table_A
DROP TABLE #Temp,#table_A
------解决方案--------------------
--這樣??
- SQL code
create table #(col varchar(100))insert into #select '1,102,' union allselect '1,105,' union allselect '2,205,' union allselect '1,101,10102,1010202,' union allselect '2,202,20203,' union all select '2,203,20303,' union allselect '1,104,10402,' union allselect '1,105,21,' union allselect '2,206,50008164,50008822,' union all select '1,105,50008164,50001705,211509,' union allselect '1,105,50008164,50001705,50008398,50008400,'select top 1000 id=identity(int,1,1)into #t from sysobjects,syscolumnsselect *from #select distinct col=substring(a.col,b.id,charindex(',',a.col+',',b.id)-b.id)from # a,#t bwhere substring(','+a.col,b.id,1)=','order by col/*col ---------------------------------------------------------------- 11011010210102021021041040210522022020320320303205206212115095000170550008164500083985000840050008822(23 row(s) affected)*/
------解决方案--------------------
- SQL code
--建立环境create table a (col1 varchar(1000))insert into aselect '1,102,' union allselect '1,105,' union all select '2,205,' union all select '1,101,10102,1010202,' union all select '2,202,20203,' union all select '2,203,20303,' union all select '1,104,10402,' union all select '1,105,21,' union all select '2,206,50008164,50008822,' union all select '1,105,50008164,50001705,211509,' union all select '1,105,50008164,50001705,50008398,50008400,' ---执行語句declare @exec varchar(8000)set @exec=''select @[email protected]+col1 from aset @exec='select '+ left(replace(@exec,',',' union select '),len(replace(@exec,',',' union select '))-len('union select'))exec (@exec)/*执行结果：--------------12211011021041052022032052061010210402202032030321150910102025000170550008164500083985000840050008822*/