格式相同的表，同一个位置的数据相加．(200)

塬

塬上独狼

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #1

sheet1,sheet2完全相同．id a b c 1 x23 id a b c 1 y23 相加的结果放入sheet3中id a b c 1 ｚ23 ｚ＝x+y

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #2

Update sheet3set a = X.A, B.=X.B, C=X.CFrom sheet3, (Select ID, Sum(a) a, sum(b) b, sum(C) c From (Select * from sheet1 union all Select * from sheet2 )Y Group By ID )XWhere sheet3.ID=X.ID

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #3

如果sheet3中没有记录，则执行下面SQLInsert into sheet3Select X.*From (Select ID, Sum(a) a, sum(b) b, sum(C) c From (Select * from sheet1 union all Select * from sheet2 )Y Group By ID )X Left Join sheet3 ON X.ID=sheet3.IDWhere sheet3.ID Is Null

 

S

songrun

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #4

先查找if exists，然后，楼上二者结合

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #5

不用if exists 了吧，我第二个SQL自动把所有在sheet3中缺少的数据给插入了。只需要先执行第一个，再执行第二个sql，就可以了，不需要if exists.

 

S

smlabc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #6

我是打酱油的

 

塬

塬上独狼

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-27

• #7

songrun大侠;sheet3中是空的．请给出完整代码，另外还有说明，我是新手，呵呵．

 

塬

塬上独狼

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-02-28

• #8

songrun大侠;sheet3中是空的．请给出完整代码，另外还有说明，我是新手，呵呵．

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-02

• #9

直接执行我的那个insert语句就可以了啊，你试验一下啊，晕！

 

塬

塬上独狼

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-03

• #10

znxia,X是什么意思 ?

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-03-03

• #11

子查询别名啊，(Select ID, Sum(a) a, sum(b) b, sum(C) c From (Select * from sheet1 union all Select * from sheet2 )Y Group By ID ) X 也可以写为(Select ID, Sum(a) a, sum(b) b, sum(C) c From (Select * from sheet1 union all Select * from sheet2 ) as Y Group By ID ) as X