100分简单的存储过程！-MSSQL教程-爱易网页

100分简单的存储过程！

日期：2014-05-18　浏览次数：20450 次

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

100分简单的存储过程！

相关资料更多>

推荐阅读更多>