现在有一个表 a1 a2 a3 a4
1 aa bb cc dd
2 aa bc ss ff
3 bb hf kf io
4 bb jf if ji
5 ...............
6 ...............就是找出列为a1中相同的值单独产生一个新表,有很多条记录,不知道会产生多少个新表??让机器自己判断
1 aa bb cc dd
2 aa bc ss ff
3 bb hf kf io
4 bb jf if ji
5 ...............
6 ...............就是找出列为a1中相同的值单独产生一个新表,有很多条记录,不知道会产生多少个新表??让机器自己判断
insert test0702 select 'aa', 'bb', 'cc', 'dd' union all select
'aa', 'bc', 'ss' ,'ff' union all select
'bb', 'hf', 'kf' , 'io' union all select
'bb' , 'jf' , 'if' , 'ji' union all select
'cc' , 'jf' , 'if' , 'ji' union all select
'cc' , 'jf' , 'if' , 'ji' --select * from test0702declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' select * into tb'+a1+' from test0702 where a1='''+a1+'''' from
(select distinct a1 from test0702) tmp
--print @sql
exec(@sql)
insert test0702 select 'aa', 'bb', 'cc', 'dd' union all select
'aa', 'bc', 'ss' ,'ff' union all select
'bb', 'hf', 'kf' , 'io' union all select
'bb' , 'jf' , 'if' , 'ji' union all select
'cc' , 'jf' , 'if' , 'ji' union all select
'cc' , 'jf' , 'if' , 'ji' declare my_cursor cursor
for
select distinct a1 from test0702
open my_cursor
declare @a1 sysname
fetch next from my_cursor into @a1
declare @i int,@sql varchar(200)
set @i=1
while(@@fetch_status=0)
begin
set @sql='select * into tb'+cast(@i as varchar(5))+' from test0702 where a1='''+@a1+''''
exec(@sql)
--print(@sql)
set @i=@i+1
fetch next from my_cursor into @a1
end
close my_cursor
deallocate my_cursor