求一SQL语句

kittast

将下面的表结构
tableA
---------------------------
新闻id       股票id
---------------------------
3456         601988,600028,HK3988,HK0386
3457         601988,600028

如何转换为下面的结构
tableB
--------------------------
新闻id       股票id
--------------------------
3456         601988
3456         600028
3456         HK3988
3456         HK0386
3457         601988
3457         600028

----------------------------------
用一个或者多个SQL语句，但是不要使用游标，因为数据量太大

michealhenry

--参考

CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
SELECT * FROM A

-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b   


SELECT
    A.ID,
    COUNTRY = SUBSTRING(A.COUNTRY, B.ID, CHARINDEX(';', A.COUNTRY + ';', B.ID) - B.ID)
FROM A, # B
WHERE SUBSTRING(';' + a.COUNTRY, B.id, 1) = ';'   
ORDER BY 1,2
GO


DROP TABLE A,#

id          country                                                                                             
----------- ----------------------------------------------------------------------------------------------------
1           中国;日本;韩国
2           美国;意大利;法国
3           德国

（所影响的行数为 3 行）

ID          COUNTRY                                                                                             
----------- ----------------------------------------------------------------------------------------------------
1           韩国
1           日本
1           中国
2           法国
2           美国
2           意大利
3           德国

（所影响的行数为 7 行）

michealhenry

查询出来后把数据插入到另一个表就可以了！

kittast

SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b  

这一句还是想不明白

fanfan1980

create table A(col1 int, col2 varchar(100))
insert A select 3456,         '601988,600028,HK3988,HK0386'
union all select 3457,         '601988,600028'

select top 8000 ID=identity(int, 1, 1) into #T from sysobjects, syscolumns


select col1, col2=cast(substring(A.col2, B.ID, charindex(',', A.col2+',', B.ID)-B.ID) as varchar(20))
from A as A, #T as B
where B.ID<=len(A.col2) and charindex(',', ','+A.col2, B.ID)=B.ID
order by col1

--result
col1        col2                 
----------- --------------------
3456        601988
3456        600028
3456        HK3988
3456        HK0386
3457        600028
3457        601988

(6 row(s) affected)

fanfan1980

kittast  


   SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b  

这一句还是想不明白
  

-------------
生成一個臨時表

1510wxs

SELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b
生成的这个临时表是用于循环的,可以把这种算法看做是一种特殊循环,这种循环不是通过WHILE循环而是通过临时表的ID与字符串中逗号出现的次序进行关联来实现循环.

sinexe

最笨的办法是
CREATE TABLE A(新闻id varchar(100),股票id VARCHAR(100))
INSERT A
SELECT '3456','601988,600028,HK3988,HK0386' UNION ALL
SELECT '3457','601988,600028'

declare @sql varchar(8000),@id varchar(8000)
select @sql = ''
select @id = ',' + 股票id,@sql = @sql + replace(@id,',',''' union all select ''' +  新闻id + ''',''')
from A
select @sql = substring(@sql,12,len(@sql)) + ''''
exec(@sql)
DROP TABLE A


（所影响的行数为 2 行）

            
---- ------
3456 601988
3456 600028
3456 HK3988
3456 HK0386
3457 601988
3457 600028

lancelover

create table A(col1 int, col2 varchar(100))
insert A select 3456,         '601988,600028,HK3988,HK0386'
union all select 3457,         '601988,600028'


create function separate (@ch varchar(8000))
returns VARCHAR(1000)
as
begin
declare @wz int
declare @sub varchar(1000)
while (charindex(',',@ch) > 0)
begin
set @wz=charindex(',',@ch)
SET @SUB=substring(@ch,1,@wz-1)
end
return(@sub)
end


select col1,dbo.separate(A.col2)
from A
order by col1

kittast

楼上的，你的过程是个死循环