sql语句怎么写啊(50分)

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #1

A表：
id date1 num1
001 20021101 110
001 20021001 100
B表：
id date2 num1
001 20021201 10
002 20021201 20

如果B表中满足以下2个条件：
1、B表的id和A表的id相同；
2、B表中的时间date2如果大于A表的date1的最大值，则B.num1＝B.num1+A.num1
这样的sql语句该怎么写？
update A,B
set b.num1=b.num1+a.num1
where a.id=b.id and
B.date2>(select max(date1) from A)
奇怪的是，id＝001的结果应该是120，但他却是220，我只要求是大于date1的最大值，
而不是大于date1的所有值？

 

A

Awen.ZW.Yang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #2

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

 

D

Dephic

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #3

昨天不是回答你了吗？怎么今天又问相同的问题啊

 

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #4

还是不对啊，关键是大于date1的[red]最大值[/red]，
而不是大于date1的所有值？

 

Q

qianwt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #5

update B set B.num1=B.num1+A.num1 [red]from A, B[/red]
where A.id=B.id and B.date2>(select max(date1) from A where A.id=B.id)

 

H

huangjiahan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #6

还是不对啊，你写的不就跟我写的一样吗，关键是就只加
一条记录

 

J

jsxjd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #7

update B
///
set b.num1=b.num1+(select top 1 num1 from a where a.id=b.id order by date1/desc)
//////////////////////////////////////
where exists(select * form a where a.id=b.id) and
/////////////////////////////////////////
B.date2>(select max(date1) from A)

 

薛

薛獅

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #8

update B Set B.num1=b.num1+(SELECT A.Num1 From A,B where A.ID=B.ID and A.Date1=
(Select MAX(A.Date1) from A,B where A.ID=B.ID))
够复杂吧？[^]

 

H

huntor

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #9

为什么以上两个语句都不能执行（系统报错）我用的是ORACLE

 

F

felex_cheng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #10

分三步，清楚些：
select id,max(date1) as date1 into #temp1 from a group by id;
select a.* into #temp2 from a inner join #temp1 on ((#temp1.id=a.id) and (#temp1.date1=a.date1));
update b set b.num1=(b.num1+#temp2.num1) from #temp2,b where b.[id]=#temp2.[id] and b.date2>#temp2.date1
以上sql语句在sql server上运行正确

 

H

huntor

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #11

怎么表A的记录ID都是001啊？

 

M

maolu28

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #12

请试一试：
update B
set b.num1=b.num1+a.num1
from B,(select id,t=Max(Date1) from A group by id) as a
where a.id=b.id and B.date2>t

good luck!

 

S

sdhxdjw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #13

UPDATE B
SET num1＝(B.num1+A.num1)
FROM B INNER JOIN (SELECT B.ID,MAX(A.DATE1) AS DATE1
FROM A INNER JOIN B ON A.ID=B.ID
GROUP BY B.ID) C
ON B.ID=C.ID AND B.DATE2>C.DATE1
INNER JOIN A ON A.ID=B.ID

 

M

maolu28

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-04

• #14

sorry!
少了一个字段，请再看
update B
set b.num1=b.num1+a.num1
from B,(select id,t=Max(Date1) from A group by id) as c,A
where a.id=b.id
and a.id=c.id
and B.date2>t

good luck!