我现在有这个一个需求,类似于体育彩票的22选5,表结构是tab1(seq,col1,col2,col3,col4,col5),tab2(seq,col1,col2,col3,……col21,col22),tab1中的所有字段都是有数据的,tab2中仅有seq是不可为空的,现在tab1中有资料,需要把tab1中的资料对应写到tab2中,假若tabl中有资料(1,1,2,3,21,22)就要对应的写到tab2的(seq,col1,col2,col3,col21,col22)中,tab1中的资料很多,如果要用一条sql语句解决,请问该如何写?
我现在有这个一个需求,类似于体育彩票的22选5,表结构是tab1(seq,col1,col2,col3,col4,col5),tab2(seq,col1,col2,col3,……col21,col22),tab1中的所有字段都是有数据的,tab2中仅有seq是不可为空的,现在tab1中有资料,需要把tab1中的资料对应写到tab2中,假若tabl中有资料(1,1,2,3,21,22)就要对应的写到tab2的(seq,col1,col2,col3,col21,col22)中,tab1中的资料很多,如果要用一条sql语句解决,请问该如何写?
set @sql = 'seq,col'+@var1+',col'+@var2...这样的.
游标每循环一次,就插入到tab2中一次
set t2.col1 = t1.col1,t2.col2 = t1.col2, ... ,t2.col22 = t1.col22
from
(select seq,
case when col1 = 1 or col2 = 1 or col3 = 1 or col4 = 1 or col5 = 1 then 1 else null end as col1,
case when col1 = 2 or col2 = 2 or col3 = 2 or col4 = 2 or col5 = 2 then 2 else null end as col2,
...
case when col1 = 22 or col2 = 22 or col3 = 22 or col4 = 22 or col5 = 22 then 22 else null end as col22)t1,tab2 t2
where t1.seq = t2.seq理论上可以写出来
效率估计会很差
if exists (select 1 from sysobjects where id=object_id('tab2') and xtype='U') drop table tab2
go
create table tab1
(
seq int,
col1 int,
col2 int,
col3 int,
col4 int,
col5 int
)
create table tab2
(
seq int,
col1 int,
col2 int,
col3 int,
col4 int,
col5 int,
col6 int,
col7 int,
col8 int,
col9 int,
col10 int,
col11 int,
col12 int,
col13 int,
col14 int,
col15 int,
col16 int,
col17 int,
col18 int,
col19 int,
col20 int,
col21 int,
col22 int
)insert into tab1
select 1,1,2,3,21,22 union all
select 2,2,3,5,10,16declare @seq int,@a int,@b int,@c int,@d int,@e int
declare @sql varchar(8000)
declare Cur_Insert cursor for
select * from tab1
open cur_insert
fetch next from cur_insert
into @seq,@a,@b,@c,@d,@e
while @@fetch_status=0
begin
set @sql='insert into tab2 (seq,[col'+rtrim(@a)+'],[col'+rtrim(@b)+'],[col'+rtrim(@c)+'],[col'+rtrim(@d)+'],[col'+rtrim(@e)+'])
values ('+rtrim(@seq)+','+rtrim(@a)+','+rtrim(@b)+','+rtrim(@c)+','+rtrim(@d)+','+rtrim(@e)+')'
exec (@sql)
fetch next from cur_insert
into @seq,@a,@b,@c,@d,@e
endclose Cur_Insert
deallocate Cur_Insertselect * from tab2
drop table tab1,tab2