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

Question

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 #tselect @sql = stuff(@sql,1,11,'')exec(@sql)
/*
ID          cityid      
----------- ----------- 
1           1
1           2
1           3
2           2
2           3
2           4
*/drop table #t

Answer 1

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

Answer 2

刚才大约算了一下，每一条记录生成的动态语句的长度<= 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_cursorselect * from #tmp
/*
ID          cityid      
----------- ----------- 
1           1
1           2
1           3
2           2
2           3
2           4
*/drop table t,#tmp

Answer 3

--不对，循环体内应该改成这样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

Answer 4

用游标实现：
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 t11open 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

Answer 5

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

Answer 6

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

Answer 7

這樣借用臨時表應該更好些。
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 BSelect
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.CityIDDrop Table #T
GO
Drop Table Table1
--Result
/*
ID CityID
1 1
1 2
1 3 
2 3
2 4
2 5
*/