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_ASELECT LEFT(colum_A,CHARINDEX(',',colum_A)-1)
FROM #table_A
WHERE CHARINDEX(',',colum_A)>1CREATE 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),'')END
SELECT * FROM #Temp
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_ASELECT LEFT(colum_A,CHARINDEX(',',colum_A)-1)
FROM #table_A
WHERE CHARINDEX(',',colum_A)>1CREATE 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),'')END
SELECT * FROM #Temp
解决方案 »
- 怎么在SQL Server中添加一个判断条件的约束
- 越简单越好
- 求一sql语句,把数据库里面的内容显示为如下格式
- 大家来看看这个查询怎么写....
- 请问这个存储错在那里?
- 数据库建表的问题 ORA-00907: 缺失右括号问题!!!???
- SQLSERVER2000严重错误,SqlDumpExceptionHandler: 进程 320 发生了严重的异常。。。
- 请教高手:sybase数据库有数据插入时同时插入到SQL Server数据库中的方法?
- 出现这样的错误:“COMPATIBILITY参数需要是8.1.0.0或者更大” 各位兄弟帮忙看看
- 请问一个SQL语句排序的问题?
- SQL Server (SQLSERVER2005) 服务因 126 (0x7E) 服务性错误而停止。事件ID:7024
- sql语句问题 急!!
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_ACREATE 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)>1END
SELECT * FROM #Temp
SELECT * FROM #table_A
DROP TABLE #Temp,#table_A
--這樣??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,syscolumnsselect *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)
*/
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('union select'))
exec (@exec)/*
执行结果:
--------------
1
2
21
101
102
104
105
202
203
205
206
10102
10402
20203
20303
211509
1010202
50001705
50008164
50008398
50008400
50008822
*/
--把所有值连成一串,逗号替换成union select,
--然后再把最后一个union select 去掉
declare @string varchar(2000)
set @string=''
select @string=@string+colum_A from a
select @string='select '
+left(replace(@string,',',' union select '),
len(replace(@string,',',' union select '))-len('union select '))
exec (@string)
create table tb(col varchar(100))
insert into tb
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 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b SELECT
distinct col = SUBSTRING(A.col, B.ID, CHARINDEX(',', A.col + ',', B.ID) - B.ID)
FROM tb A, tmp B
WHERE SUBSTRING(',' + a.col, B.id, 1) = ','
ORDER BY col
GOdrop table tb , tmp/*col
---------------------------------------------------------------------------------------------------- 1
101
10102
1010202
102
104
10402
105
2
202
20203
203
20303
205
206
21
211509
50001705
50008164
50008398
50008400
50008822(所影响的行数为 23 行)*/
insert into tb
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 distinct b.col
FROM(
SELECT [col] = CONVERT(xml,
'<root><v>' + REPLACE([col], ',', '</v><v>') + '</v></root>')
FROM tb
)A
OUTER APPLY(
SELECT col = N.v.value('.', 'varchar(100)')
FROM A.[col].nodes('/root/v') N(v)
)B
order by b.col
drop table tb/*
col
-----1
101
10102
1010202
102
104
10402
105
2
202
20203
203
20303
205
206
21
211509
50001705
50008164
50008398
50008400
50008822(23 行受影响)
*/
drop table b
create table b (col1 int)
declare @str1 varchar(300), @str2 varchar(100)
set @str1=''
set @str2=''
declare cs CURSOR for select agentpath from CommodityBargain where agentpath <> ''
open cs
FETCH NEXT FROM cs into @str1
while (@@fetch_status = 0)
begin
while(charindex(',',@str1)>0)
begin
set @str2 = replace(left(@str1,charindex(',',@str1)),',','')
insert into b select @str2
set @str1 = substring(@str1,len(@str2)+2,len(@str1)-(len(@str2)+1))
end
FETCH NEXT FROM cs into @str1
end
CLOSE cs
DEALLOCATE cs
select distinct col1 from b order by col1 asc
(charindex(',',reverse([agentpath]),2)-2)<=0
then len([agentpath])-1 else charindex(',',reverse([agentpath]),2)-2 end)) as int)
from CommodityBargain where agentpath <> ''
order by agentpath asc
(charindex(',',reverse([agentpath]),2)-2)<=0
then len([agentpath])-1 else charindex(',',reverse([agentpath]),2)-2 end)) as int)
from CommodityBargain where agentpath <> ''
order by agentpath asc
drop table b
create table b (col1 int)
declare @str1 varchar(300), @str2 varchar(100)
set @str1=''
set @str2=''
declare cs CURSOR for select agentpath from CommodityBargain where agentpath <> ''
open cs
FETCH NEXT FROM cs into @str1
while (@@fetch_status = 0)
begin
while(charindex(',',@str1)>0)
begin
set @str2 = replace(left(@str1,charindex(',',@str1)),',','')
insert into b select @str2
set @str1 = substring(@str1,len(@str2)+2,len(@str1)-(len(@str2)+1))
end
FETCH NEXT FROM cs into @str1
end
CLOSE cs
DEALLOCATE cs
select distinct col1 from b order by col1 asc
insert into tb
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,'
go
/*用replace来做简单吧*/
declare @str varchar(8000)select @str='select '
select @str=@str+replace(col,',',' union select ') from tb
select @str=left(@str,len(@str)-13)
execute(@str)go
drop table tb
insert into tb
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,'
go
/*用replace来做简单吧*/
declare @str varchar(8000) select @str='select '
select @str=@str+replace(col,',',' union select ') from tb
select @str=left(@str,len(@str)-13)
execute(@str) go
drop table tb
执行很快啊
利用了union的特点
关注中