• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

关于SQL面试题,帮忙解决

hbxfgj 2008-02-27 06:15:44
,现有表bill和表payment结构如下,
两表通过pay_id关联:    
bill表――bill_id是帐单标识,charge为帐单金额,pay_id为付款编号,PK:bill_id
    bill_id   INTEGER    
charge   INTEGER    
pay_id   INTEGER      
 payment表――pay_id为付款编号,charge为付款金额,PK:pay_id    
pay_id   INTEGER    
charge   INTEGER        
要求更新payment使charge=bill表中同一pay_id的charge之和。  
...全文
91 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
/*
create table bill
(
bill_id int,
charge int,
pay_id int
)
create table payment
(
pay_id int,
charge int
)
insert bill values(1,200,1)
insert bill values(2,200,1)
insert bill values(3,200,1)
insert bill values(4,200,1)
insert bill values(5,200,1)
insert bill values(6,200,1)
insert bill values(1,200,2)
insert bill values(2,200,2)
insert bill values(3,200,2)
insert bill values(4,200,2)
insert bill values(5,200,2)
insert bill values(6,200,2)

insert payment(pay_id) values(1)
insert payment(pay_id) values(2)*/

update payment set charge = bb.charge from payment aa,
(select b.pay_id,sum(isnull(a.charge,0)) charge from bill a left join payment b on a.pay_id=b.pay_id group by b.pay_id) bb
where aa.pay_id=bb.pay_id
回复
pt1314917 2008-02-28
               

update payment set charge=(select sum(charge) from bill where pay_id=a.pay_id) from payment a

回复
hbxfgj 2008-02-28
上边的我试了不可以,有语法上的错误
update payment a , (select sum(charge) sCharge,pay_id from bill GROUP by pay_id) b
set a.charge=b.sCharge
where a.pay_id=b.pay_id
回复
tim_spac 2008-02-27
update a
set charge = b.sumcharge
from payment a
join (select pay_id, sumcharge=sum(charge)
from bill
group by pay_id) as b on a.pay_id=b.pay_id
回复
fcuandy 2008-02-27
UPDATE a SET charge = sCharge
FROM payment a
INNER JOIN
(SELECT SUM(charge) sCharge,pay_id FROM bill GROUP BY payer_id) b
ON a.pay_id=b.pay_id
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-02-27 06:15
社区公告
暂无公告