典型sql查询求解,比较难..

[no_mIss]

Table1
id CityID
1 1,2,3
2 3,4,5


Table2
id CityID
1 1
1 2
1 3
2 3
2 4
2 5

----------------
我的解决方法:
create table #T(id int,cityid varchar(200))
go
insert into #T select 1,'1,2,3' union all select 2 , '2,3,4'
go
declare @T table (newid int identity(1,1) primary key ,id int,cityid varchar(200))
declare @T2 table (id int,cityid varchar(200))
insert into @T select id,cityid+',' from #T order by id asc
select id,cityid from @T
declare @i int
select @i = 1
while @i<=(select max(newid) from @T)
begin
while (select charindex(',',cityid) from @T where newid = @i)>0
begin
insert into @T2
select (select id from @T where newid = @i) as id,
(select substring(cityid,0,(charindex(',',cityid))) from @T where newid = @i) as cityid
update @T set cityid = substring(cityid,charindex(',',cityid)+1,len(cityid)) from @T where newid = @i
end
select @i = @i + 1
end
select * from @T2 order by id

感觉比较麻烦,期待更好的方法..

[mengmou]

create table #T(id int,cityid varchar(200))
go
insert into #T select 1,'1,2,3' union all select 2 , '2,3,4'
go
declare @sql varchar(8000) set @sql = ''


select @sql = @sql + ' union all select '+convert(varchar(10),id)+'as ID,'
+replace(cityid,',','as cityid union all select '
+convert(varchar(10),id)+',')
from #t

select @sql = stuff(@sql,1,11,'')

exec(@sql)
/*
ID cityid
----------- -----------
1 1
1 2
1 3
2 2
2 3
2 4
*/

drop table #t

[no_mIss]

这个方法我想到了,但由于拼字符串,所以会有长度的问题,如果长度太长,拼sql会有问题

[mengmou]

刚才大约算了一下，每一条记录生成的动态语句的长度<= len(cityid) + 100，这样的话可以这样写。刚才用大数据量测试过了，没问题。


select as ID,'' as cityid union all select ,'' as cityid union all select ,'' union allcreate table T(id int,cityid varchar(200))
create table #tmp(id int,cityid varchar(200))
go
insert into T select 1,'1,2,3' union all select 2 , '2,3,4'
go
declare @sql varchar(8000),@id int,@cityid varchar(200) select @sql = ''

declare t_cursor cursor for
select id,cityid from T
open t_cursor
fetch next from t_cursor into @id,@cityid
while @@fetch_status = 0
begin
if len(@sql) + len(@cityid) +100 < 8000
select @sql = @sql + ' union all select '
+convert(varchar(10),id)+' as ID,'''
+replace(cityid,',',''' as cityid union all select '
+convert(varchar(10),id)+',''')+''''
from t where id = @id
else
begin
select @sql = stuff(@sql,1,11,'')
insert #tmp
exec(@sql)
set @sql = ''
end
fetch next from t_cursor into @id,@cityid
end
select @sql = stuff(@sql,1,11,'')
insert #tmp
exec(@sql)
close t_cursor
deallocate t_cursor

select * from #tmp
/*
ID cityid
----------- -----------
1 1
1 2
1 3
2 2
2 3
2 4
*/

drop table t,#tmp

[mengmou]

--不对，循环体内应该改成这样

begin
if len(@sql) + len(@cityid) +100 < 8000
select @sql = @sql + ' union all select '
+convert(varchar(10),id)+' as ID,'''
+replace(cityid,',',''' as cityid union all select '
+convert(varchar(10),id)+',''')+''''
from t where id = @id
else
begin
select @sql = stuff(@sql,1,11,'')
insert #tmp
exec(@sql)
set @sql = ''
select @sql = @sql + ' union all select '
+convert(varchar(10),id)+' as ID,'''
+replace(cityid,',',''' as cityid union all select '
+convert(varchar(10),id)+',''')+''''
from t where id = @id
end
fetch next from t_cursor into @id,@cityid
end

[marco08]

用游标会快点

[ojuju10]

用游标实现：
create table t11 ( id int ,cityid varchar(50))
insert into t11 select 1,'1,2,3'
union all select 2 ,'4,5,6'



declare @id int,@name varchar(50)
declare roy cursor for
select * from t11

open roy
fetch next from roy into @id,@name
while @@fetch_status=0
begin
declare @tab table (id int,cityid varchar(50))
while charindex(',',@name)>0
begin
insert @tab select @id ,left(@name,charindex(',',@name)-1)
set @name=stuff(@name,1,charindex(',',@name),'')
end
insert into @tab select @id,@name
fetch next from roy into @id,@name
end
close roy
deallocate roy
select * from @tab

[jinanjiang]

up

[no_mIss]

to marco08 :为什么游标会快点?
to ojuju10 :可以
to mengmou()mengmou() : 幸苦

[mengmou]

用游标只遍历表一遍
用循环要遍历max(newid)遍

[paoluo]

這樣借用臨時表應該更好些。


Create Table Table1
(id Int,
CityID Varchar(100))
Insert Table1 Select 1, '1,2,3 '
Union All Select 2, '3,4,5'
GO
Select Top 1000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns B

Select
A.ID,
CityID = Substring(A.CityID, B.ID, CharIndex(',', A.CityID + ',', B.ID) - B.ID)
From Table1 A, #T B
Where Substring(',' + a.CityID, B.id, 1) = ','
Order By A.ID, A.CityID

Drop Table #T
GO
Drop Table Table1
--Result
/*
ID CityID
1 1
1 2
1 3
2 3
2 4
2 5
*/

[mengmou]

唉，把最经典的一个方法忘了。

[no_mIss]

嘿嘿,方法收集完毕,谢谢几位老大了