问一个复杂的SQL解析的问题
table1 这张表有两个字段,字段名分别为 row1 row2
数据的形式是这样的:
row1 row2
1 a,b,c,
2 d,e,f,
3 g,h,i
我想解析row2里的数据,转到一张新表table2(两个字段名为newrow1 newrow2)里,成这种方式:
newrow1 newrow2
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
3 i
用SQL语句怎么写好呢? 数据量大概一万多条.
谢谢了!!
[解决办法]
declare @a table(row1 int, row2 varchar(100))
insert @a select 1 , 'a,b,c '
union all select 2 , 'd,e,f '
union all select 3 , 'g,h,i '
select top 200 id=identity(int,1,1) into # from syscolumns a ,syscolumns b
select row1,substring(row2+ ', ',id,charindex( ', ',row2+ ', ',id+1)-id) b
from #,@a
where substring( ', '+row2,id,1)= ', '
drop table #
[解决办法]
declare @t table(row1 int,row2 varchar(20))
insert into @t values(1, 'a,b,c, ')
insert into @t values(2, 'd,e,f, ')
insert into @t values(3, 'g,h,i ')
select
row1,
parseName(row2,3) as row2,
parseName(row2,2) as row3,
parseName(row2,1) as row4
from
(select row1,replace(case when right(row2,1)= ', ' then left(row2,len(row2)-1) else row2 end, ', ', '. ') as row2 from @t) t
/*
row1 row2 row3 row4
----------- ------------ ----------- -------------
1 a b c
2 d e f
3 g h i
*/
select
row1,
parseName(row2,3) as row2
from
(select row1,replace(case when right(row2,1)= ', ' then left(row2,len(row2)-1) else row2 end, ', ', '. ') as row2 from @t) t
union all
select
row1,
parseName(row2,2)
from
(select row1,replace(case when right(row2,1)= ', ' then left(row2,len(row2)-1) else row2 end, ', ', '. ') as row2 from @t) t
union all
select
row1,
parseName(row2,1)
from
(select row1,replace(case when right(row2,1)= ', ' then left(row2,len(row2)-1) else row2 end, ', ', '. ') as row2 from @t) t
/*
row1 row2
----------- ------------
1 a
2 d
3 g
1 b
2 e
3 h
1 c
2 f
3 i
*/
------解决方案--------------------
select a.row1,b.row2 from
(
select row1=1,row2= 'a,b,c ' union all
select 2, 'd,e,f ' union all
select 3, 'g,h,i '
) a join
(
select row2= 'a ' union all
select row2= 'b ' union all
select row2= 'c ' union all
select row2= 'd ' union all
select row2= 'e ' union all
select row2= 'f ' union all
select row2= 'g ' union all
select row2= 'h ' union all
select row2= 'i '
) b
on charindex( ', '+b.row2+ ', ', ', '+a.row2+ ', ')> 0
[解决办法]
create table tb(row1 int, row2 varchar(32))
go
create table tb2(newrow1 int, newrow2 varchar(16))
go
insert tb select 1, 'a,b,c '
union all select 2, 'd,e,f '
union all select 3, 'g,h,i '
go
create function fnParse(@row1 varchar(10), @row2 varchar(32))
returns varchar(256) as
begin
declare @result varchar(256)
set @row2 = @row2
set @result = @row1 + ', ' ' ' + left(@row2,charindex( ', ',@row2+ ', ')-1) + ' ' ' '+char(13)+char(10)
set @row2 = stuff(@row2,1,charindex( ', ',@row2), ' ')+ ', '
while(charindex( ', ',@row2)> 0)
select @result=@result+ ' union all select '+@row1+ ', ' ' '+left(@row2,charindex( ', ',@row2)-1)+ ' ' ' '+char(13)+char(10)
,@row2=stuff(@row2,1,charindex( ', ',@row2), ' ')
return @result
end
go
declare @sql varchar(8000)
set @sql = 'insert tb2 select '
select @sql = @sql+ dbo.fnParse(cast(row1 as varchar),row2) + ' union all select '
from tb
set @sql=left(@sql, len(@sql)-len( ' union all select '))
--print @sql
exec(@sql)
select * from tb2
drop table tb
drop table tb2
drop function fnParse
[解决办法]
用表变量法可以的！
create table #test (row1 varchar(500), row2 varchar(7500))
insert #test select 'SN/T 0368-1995 ', 'GB 2727,GBn 240,GB 5749,GB 4789.17,GB 7104,GB 5009.27,GB 9695.10,GB 7718,GB 5033,GB 4456,GB 9691,GB 9692,GB 6388,SN 0428,SN 0168,SN 0169,SN 0170,SN 0172, '
insert #test select 'SN 0369-1995 ', 'GB 2721,GB 2760,GB 5009.5,GB 5009.33,GB 5033,GB 7741,GB 9691,GB 9695.7,GB 9695.8,GB 9695.14,GB 9695.15,SN 0168,SN 0169,SN 0170,SN 0172,SN 0411, '
select top 1000 id=identity(int,1,1) into #t from sysobjects,syscolumns
select row1,row2=substring(row2,b.id,charindex( ', ',row2,b.id)-b.id)
from #test a,#t b
where substring( ', '+left(row2,len(row2)-1),b.id,1)= ', '
[解决办法]
执行完后t1中的数据就为空了，把t3的再导到t1中
insert t1(row1,row2) select row1,row2 from t3
[解决办法]
libin_ftsafe(子陌红尘：TS for Banking Card) 的办法可行，我也刚准备回复，刚看了libin_ftsafe(子陌红尘：TS for Banking Card) 的另外一个帖子，用了parsename这个函数，赶紧用的真是巧妙！
别人的我看都认为是abc...g的组合三位字符串了，其实lz有数据量1w呢，情况可能不是那也，而且可能逗号间隔的字符串不是一个字符，建立lz改造一下libin_ftsafe(子陌红尘：TS for Banking Card) 的方法，每次取逗号间隔的字符长度的len，再substring应该就可以了
[解决办法]
/*树上的鸟方法貌是最是最简单的，小楼和其他朋友在上面已经解释的很清楚了
我上次写给你的那个方法也可以...
*/
/*----- 引用楼上的楼上...------*/
---创建测试环境
Create Table #T (row1 varchar(100), row2 varchar(8000))
Insert #T Select 'SN/T 0368-1995 ', 'GB 2727,GBn 240,GB 5749,GB 4789.17,GB 7104,GB 5009.27,GB 9695.10,GB 7718,GB 5033,GB 4456,GB 9691,GB 9692,GB 6388,SN 0428,SN 0168,SN 0169,SN 0170,SN 0172 '
Union All Select 'SN 0369-1995 ', 'GB 2721,GB 2760,GB 5009.5,GB 5009.33,GB 5033,GB 7741,GB 9691,GB 9695.7,GB 9695.8,GB 9695.14,GB 9695.15,SN 0168,SN 0169,SN 0170,SN 0172,SN 0411, '
Select Top 8000 ID=Identity(Int,1,1) Into # From sysColumns,sysObjects
---更新如果最后一个字符是 ', ',去掉
Update #T Set row2=Left(row2,Len(row2)-1) Where Right(row2,1)= ', '
Select * From #T
---查询并把结果Into一张新表Table2
Select
row1 As newrow1,
Substring(row2+ ', ',id,charindex( ', ',row2+ ', ',id+1)-id) As newrow2
Into Table2
From
#T As A,# As B
Where
Substring( ', '+row2,id,1)= ', ' And id <= len(row2)
Order By
row1
---查询新表Table2的结果
Select * From Table2
---删除测试环境
Drop Table #,#T,Table2
---结果
/*
newrow1 newrow2
---------------- -------------
SN 0369-1995 GB 2721
SN 0369-1995 GB 2760
SN 0369-1995 GB 5009.5
...
...
...
SN/T 0368-1995 SN 0172
（所影响的行数为 34 行）
*/
[解决办法]
create table tb(row1 int,row2 varchar(10))
insert into tb values(1, 'a,b,c ')
insert into tb values(2, 'd,e,f ')
insert into tb values(3, 'g,h,i ')
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000
id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
-- 以上生成一个临时表
SELECT
A.row1 newrow1,
newrow2 = SUBSTRING(A.row2, B.ID, CHARINDEX( ', ', A.row2 + ', ', B.ID) - B.ID)
FROM tb A, # B
WHERE SUBSTRING( ', ' + a.row2, B.id, 1) = ', '
ORDER BY 1,2
GO
drop table tb,#
/*
newrow1 newrow2
----------- ----------
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
3 i
（所影响的行数为 9 行）
*/
