日期:2014-05-18  浏览次数:20435 次

100分求一个简单的存储过程!!!!!!
下面每一行都是表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 all
select '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,'

select top 1000 id=identity(int,1,1)into #t from sysobjects,syscolumns

select *from #

select  distinct
    col=substring(a.col,b.id,charindex(',',a.col+',',b.id)-b.id)
from # a,#t b
where substring(','+a.col,b.id,1)=','
order by col

/*
col                                                                                                  
---------------------------------------------------------------- 

1
101
10102
1010202
102
104
10402
105
2
202
20203
203
20303
205
206
21
211509
50001705
50008164
50008398
50008400
50008822

(23 row(s) affected)
*/

------解决方案--------------------
SQL code
--建立环境
create table  a (col1 varchar(1000))
insert into a
select '1,102,' union all
select '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 @exec=@exec+col1 from a
set @exec='select '+ left(replace(@exec,',',' union  select '),len(replace(@exec,',',' union  select '))-len('un