现有两个表TB1,TB2,有如下数据
TB1
FD SP
A 111
B 222
C 222
D 333
E 333
. ...
ZTB2
SP
111
222
333
...
我想把TB2表的数据插入到TB1表,TB1.FD每个值都要有TB2.SP的值,但不要重复插入
结果如下
TB1
FD SP
A 111
A 222
A 333
B 111
B 222
B 333
...
TB1
FD SP
A 111
B 222
C 222
D 333
E 333
. ...
ZTB2
SP
111
222
333
...
我想把TB2表的数据插入到TB1表,TB1.FD每个值都要有TB2.SP的值,但不要重复插入
结果如下
TB1
FD SP
A 111
A 222
A 333
B 111
B 222
B 333
...
update a
set a.sp=b.sp
from tb1 a,tb2 b
go
create table tb1([FD] varchar(10),[SP] int)
insert tb1 select 'A',111
union all select 'B',222
union all select 'C',222
union all select 'D',333
union all select 'E',333
go
if object_id('tb2') is not null drop table tb2
go
create table tb2([SP] int)
insert tb2 select 111
union all select 222
union all select 333
goinsert tb1
select *
from (Select * from (select distinct fd from tb1) a,tb2) as t
where not exists(select 1 from tb1 where fd=t.fd and sp=t.sp)select * from tb1 order by fd
/*
FD SP
---------- -----------
A 222
A 333
A 111
B 222
B 111
B 333
C 111
C 333
C 222
D 333
D 111
D 222
E 111
E 222
E 333(15 行受影响)
*/
create table #Tb1(FD nvarchar(2), sp nvarchar(10))
insert #tb1 values('A' ,'111')
insert #tb1 values('B' ,'222')
insert #tb1 values('C' ,'222')
insert #tb1 values('D' ,'333')
insert #tb1 values('E' ,'333')
create table #Tb2(Sp nvarchar(10))
insert #tb2 values('111')
insert #tb2 values('222')
insert #tb2 values('333') insert #tb1
select a.FD,b.sp from #tb1 a join #tb2 b on a.sp <> b.sp
order by a.fdselect * from #tb1 order by fd,sp
/*
FD sp
---- ----------
A 111
A 222
A 333
B 111
B 222
B 333
C 111
C 222
C 333
D 111
D 222
D 333
E 111
E 222
E 333(15 row(s) affected)*/
insert #tb1 select 'A',111
union all select 'B',222
union all select 'C',222
union all select 'D',333
union all select 'E',333
gocreate table #tb2([SP] int)
insert #tb2 select 111
union all select 222
union all select 333
goinsert into #tb1 select * from
(
select b1.FD,b2.sp as sp2 from #tb1 b1,#tb2 b2 where b1.[SP] not in (b2.[SP])
)Tselect * from #tb1FD SP
---------- -----------
A 111
B 222
C 222
D 333
E 333
A 222
A 333
B 111
B 333
C 111
C 333
D 111
D 222
E 111
E 222(15 行受影响)
insert #tb1 select 'A',111
union all select 'B',222
union all select 'C',222
union all select 'D',333
union all select 'E',333
gocreate table #tb2([SP] int)
insert #tb2 select 111
union all select 222
union all select 333
goinsert into #tb1 select * from
(
select b1.FD,b2.sp as sp2 from #tb1 b1,#tb2 b2 where b1.[SP] not in (b2.[SP])
)T select * from #tb1 order by FD,[SP]FD SP
---------- -----------
A 111
A 222
A 333
B 111
B 222
B 333
C 111
C 222
C 333
D 111
D 222
D 333
E 111
E 222
E 333(15 行受影响)
劳驾问一下,上面这句代码是什么意思?
LZ就一句话用Tb1, Tb2无条件连接, 产生笛卡尔乘积的记录数, 再distinct即可
select distinct FD, SP from (select z.FD, m.SP from Tb1 as z, Tb2 as m) as s
这句有点看不明白
不过感觉6楼的 满有想法的