1、lfy1为目标表(你想要插入的另一个表),lfy2为源表 select columnname into lfy1 from lfy2 2、比如A表中的result由B表的R1和C表的R2相乘得到: update A set result=b1.R1*c1.R2 from A as a1 inner join B as b1 on a1.列Z=b1.列X inner join C as c1 on b1.列X=c1.列Y and c1.列S>0
insert into 目标表名(目标字段) select 所要查的字段 from 源表名
1.将一个表中的某列字段全部复制(插入)到另一个表中,求SQL语句。是类EXCEL的复制吗?两者之间没有关联吗?2.一个表中的值是另几个表中的值的运算结果。例如: 假设更新A表的aa字段,和其他表之间用id字段关联 update A set aa=b.cc+d.ee+f.jj from A,b,c,d,f where a.id=b.id and a.id=c.id and a.id=d.id and a.id=f.id
longji(龙寂) 第2个题目如果用INNER JOIN,不是会过滤掉一些数据吗?例如 A 中的某个值,其对应的因数R1或R2没有,怎么办?
如果是单纯复制的话,就要用到临时表: 例如: 设有表tba 表tbb bh name value 1 aa sd 2 bb ei 3 ee要将tbb的value复制到表tba select id=identity(1,1),bh,name into #t2 from tba select id=identity(1,1) value into #t1 from tbb drop table tba select a.bh,a.name,b.value into tba from #t1 a full join #t2 b on a.id=b.id drop table #t1,#t2
into b
from a
select columnname into lfy1 from lfy2
2、比如A表中的result由B表的R1和C表的R2相乘得到:
update A
set result=b1.R1*c1.R2
from A as a1 inner join B as b1 on a1.列Z=b1.列X
inner join C as c1 on b1.列X=c1.列Y and c1.列S>0
select 所要查的字段 from 源表名
update A set aa=b.cc+d.ee+f.jj
from A,b,c,d,f
where a.id=b.id and a.id=c.id and a.id=d.id and a.id=f.id
例如:
设有表tba 表tbb
bh name value
1 aa sd
2 bb ei
3 ee要将tbb的value复制到表tba
select id=identity(1,1),bh,name into #t2 from tba
select id=identity(1,1) value into #t1 from tbb
drop table tba
select a.bh,a.name,b.value into tba
from #t1 a full join #t2 b
on a.id=b.id
drop table #t1,#t2