sql语句怎么写啊(50分)

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #1

A表：
id date1 num1 num2 num3
001 20021101 20 0 10
001 20021001 100 10 100
B表：
id date2 num1 num2 num3
001 20021201 10 10 ?
002 20021201 20 100 80

如果B表中满足以下2个条件：
1、B表的id和A表的id相同；
2、B表中的时间date2如果大于A表的date1的最大值，则B.num3＝B.num2-B.num1+A.num3
这样的sql语句该怎么写？

 

D

duanhb

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #2

update b set B.num3＝B.num2-B.num1+A.num3 where b.id=a.id and b.date2>a.data1

 

Q

qianwt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #3

update B set B.num3＝B.num2-B.num1+A.num3 from A, B
where A.id=B.id and B.date2>A.date1

 

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #4

是相同id的最大值，注意是最大值

 

L

ljun

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #5

关注！

 

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #6

是相同id的date1最大值，注意是最大值

 

J

jsxjd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #7

a.sum3 是和吧

 

G

goodfriend1

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #8

update table1
set b.num3=b.num2-b.num1+a.num3
from table1 a,table2 b
where a.id=b.id and
a.date2<(select max(date1) from table1)

 

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #9

求某个字段的最大值，怎么写啊

 

J

jsxjd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #10

Update b set num3=num3-num1+(select sum(sum3) from A)
where exists(Select * from A where a.id=b.id)
and ((select max(date1) from a)<b.date2)

 

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #11

接受答案了.

 

A

Awen.ZW.Yang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #12

UPDATE B SET num3=(B.num2-B.num1+A.num3)
FROM B JOIN A ON B.ID=A.ID
AND B.DATE2 > (SELECT MAX(DATE1) FROM A WHERE A.ID=B.ID)