典型sql查询求解,比较难..
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
感觉比较麻烦,期待更好的方法..
[解决办法]
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
[解决办法]
用游标会快点
[解决办法]
用游标实现：
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
[解决办法]
這樣借用臨時表應該更好些。
Create Table Table1
(idInt,
CityIDVarchar(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
/*
IDCityID
11
12
13
23
24
25
*/
