这条SQL怎样写(25分)

D

devexpress

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-24

• #1

hyk表

Password, yfmoney (应付)
1 0
2 0
3 0

xsb表
Password dj (单价) sl（数量)
1 1 5
1 1 10
2 2 10
2 1 10
3 1 10

怎样得到下样的结果
Password, yfmoney (应付)
1 15
2 30
3 10

也就是把xsb里　按
sum(dj*sl), group by password, where hyk.password=xsb.password
的条件汇总
把最后的金额加到hyk表里。

谢谢

 

D

devexpress

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-24

• #2

up

 

D

devexpress

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-24

• #3

update hyk
set hyk.yfmoney=hyk.yfmoney+D.yfmoney
From hyk left join
(Select Password,sum(dj*sl) as yfmoney from xsb group by Password) as D
on hyk.ghbh=D.password

自己解决。不知哪位有更好的算法没有。

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-25

• #4

update hyk a
set
a.yfmoney=a.yfmoney+(b.dj*b.sl)
from xsb b
where a.password=b.password

 

P

pengjinlongex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-25

• #5

update hyk
set
yfmoney=b.sumx
from (select password,sum(dj*sl) as sumx from xsb group by password) b
where hyk.password=b.password

 

D

dae

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-25

• #6

做视图不可以吗？
create view hykandxsb as
select a.password password,sum(b.dj*b.sl) yfmoney from hyk a,xsb b
where a.password = b.password
group by a.password
order by a.password;
问一下你用的什么数据库？

 

竹

竹寨

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-25

• #7

updata hyk
set yfmoney=(select sum(dj*sl) from xsk where hyk.password=xsk.password)

 

J

jm790808

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-25

• #8

和楼上看法一样！