求sql语句(100分)

L

lhy9988

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #1

库A:|A1 A2 A3 | 库B: |A1 A2 A3 |
|a aa aaa | |a aa ddd |
|b bb bbb | |b bb eee |
|c cc ccc |
生成库C:|A1 A2 A3 |
|a aa aaa+ddd |
|b bb bbb+eee |
|c cc ccc |
怎么写sql?

 

H

htw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #2

用两条SQL语句:
insert into C select * from A
Update C Set C.A3=C.A3+B.A3 where C.A1=B.A1 AND C.A2=B.A2

 

F

free_knight

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #3

一句就行
insert into C (A1,A2,A3 )
values(select A.A1,A.B1,A.C1+B.C1
from A,B
where A.A1=B.A1 AND A.A2=B.A2)

 

L

lhy9988

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #4

htw：pardox数据库
free_knight:c cc ccc会丢矢

 

S

SuperJS

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #5

用 left join

 

天

天真

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #6

insert into C (A1,A2,A3 )
values(select A.A1,A.B1,a.a3+sum(isnull(a.a1,0))
from A,B
where A.a1*=b.a1)
这样就行了：）

 

天

天真

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #7

insert into C (A1,A2,A3 )
values(select A.A1,A.a2,A.a3+sum(isnull(b.a3,0))
from A,B
where A.A1*=b.a1)
这样就行了：）

 

L

lhy9988

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #8

SuperJS：jion ?写具体语句好吗？

 

J

jrq

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #9

>来自：htw, 时间：2002-1-9 15:59:00, ID：845298
>用两条SQL语句:
>insert into C select * from A
>Update C Set C.A3=C.A3+B.A3 where C.A1=B.A1 AND C.A2=B.A2

pardox数据库不可以这样使用吗？
这样写是最易读懂的～

 

F

free_knight

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #10

一句就行
insert into C (A1,A2,A3 )
values(select A.A1,A.B1,A.C1+B.C1
from A left join B
on (A.A1=B.A1 AND A.A2=B.A2) )

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #11

三句
一、insert into C select * from A
二、Update C Set C.A3=C.A3+B.A3 where C.A1=B.A1 AND C.A2=B.A2
三、insert into c select b.* from b
where not (a1 in (select a1 from a) )
and not (a2 in (select a2 from a))

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #12

insert into C (A1,A2,A3 )
select A.A1,A.a2,A.a3||B.a3
from A left join B
on (A.A1=B.A1 AND A.A2=B.A2)

 

L

lhy9988

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #13

pardox数据库，update 涉及到两个库就出现字段错误信息，各位可以使一下

 

C

chenliang_fly

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #14

insert into C (A1,A2,A3 )
(
select a.a1,a.a2,sum(a.a3)+sum(b.a3)
from a left join b on a.a1=b.a1 and a.a2=b.a2 group by a.a1,a.a2
)

 

天

天真

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #15

我的试了吗？

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #16

我的代码测试通过，没有遇到问题啊
insert into C (A1,A2,A3 )
select A.A1,A.a2,A.a3||B.a3
from A left join B
on (A.A1=B.A1 AND A.A2=B.A2)

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #17

修正:
insert into C (A1,A2,A3 )
select A.A1,A.a2,CAST(A.a3||B.a3 as VARCHAR(10))
from A left join B
on (A.A1=B.A1 AND A.A2=B.A2)

要CAST将a3|a3 转成字符型，
其中长度多少根据实际情况而定
只要长度不超过A3可容纳的长度就可以了。
也要注意如果给的不够长，会自动剪掉后面部份。

 

L

lhy9988

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #18

join成功

 

L

lhy9988

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #19

多人接受答案了。