日期:2014-05-18  浏览次数:20556 次

再次提问update,刚才表述有错误,更正求助
现有a,b两个表  
a表结构如下 
id 种类 单价 数量 
1 a 2.3 3 
  b 26 6
  c 3.6 25
2 a 2.5 9 
  b 36 5
  c 0.36 20
3 a 6.5 20 
  d 9.6 3 
...... 

b表结构如下 
id 总费用 
1 ? 

现在如何使用sql的update功能 把a表的价格和数量的积 在 sum 后 通过id分别对应到b表的总费用上? 


急!等求答案

------解决方案--------------------
update b 
set 总费用 = c.总费用 
from b,(select id , sum(amount*price) 总费用 from a group by id) c 
where b.id = c.id 

------解决方案--------------------
--那里回了
update B 
set 总费用=T.费用
from B, 
(select id,sum(a.amount*a.price) as 费用
from A group by id) T 
where B.id=T.id
------解决方案--------------------
create table a(id int,种类 varchar(10),单价 decimal(18,1) ,数量 int)
insert into a values(1,'a',2.3 ,3 ) 
insert into a values(1,'b',26 ,6 )
insert into a values(1,'c',3.6 ,25) 
insert into a values(2,'a',2.5 ,9 ) 
insert into a values(2,'b',36 ,5 )
insert into a values(2,'c',0.36,20) 
insert into a values(3,'a',6.5 ,20)
insert into a values(3,'d',9.6 ,3 ) 
create table b(id int,总费用 decimal(18,1))
insert into b values(1,null)
insert into b values(2,null)
insert into b values(3,null)
go
--替换
update b
set 总费用 = c.总费用
from b,(select id , sum(单价*数量) 总费用 from a group by id) c
where b.id = c.id
--查询
select * from b
--删除测试表
drop table a,b

/*
id 总费用
----------- -------------------- 
1 252.9
2 210.5
3 158.8

(所影响的行数为 3 行)
*/
------解决方案--------------------
begin tran
create table #a(aid int,style nchar(2),unitCost numeric(12,2),amount int)
insert into #a(aid,style,unitCost,amount)values(1,'a',2.3,3)
insert into #a(aid,style,unitCost,amount)values(1,'b',26,6)
insert into #a(aid,style,unitCost,amount)values(1,'c',3.6,25)
insert into #a(aid,style,unitCost,amount)values(2,'a',2.5,9)
insert into #a(aid,style,unitCost,amount)values(2,'b',36,5)
insert into #a(aid,style,unitCost,amount)values(2,'c',0.36,20)
insert into #a(aid,style,unitCost,amount)values(3,'a',6.5,20)
insert into #a(aid,style,unitCost,amount)values(3,'d',9.6,3)
select * from #a
select aid,sum(unitCost*amount) total from #a group by aid
rollback tran
------解决方案--------------------
begin tran
create table #a(aid int,style nchar(2),unitCost numeric(12,2),amount int)
insert into #a(aid,style,unitCost,amount)values(1,'a',2.3,3)
insert into #a(aid,style,unitCost,amount)values(1,'b',26,6)
insert into #a(aid,style,unitCost,amount)values(1,'c',3.6,25)
insert into #a(aid,style,unitCost,amount)values(2,'a',2.5,9)
insert into #a(aid,style,unitCost,amount)values(2,'b',36,5)
insert into #a(aid,style,unitCost,amount)values(2,'c',0.36,20)
insert into #a(aid,style,unitCost,amount)values(3,'a',6.5,20)
insert into #a(aid,style,unitCost,amount)values(3,'d',9.6,3)
select * from #a
select aid,sum(unitCost*amount) total from #a group by aid

create table #b(bid int,totalCost numeric(12,2))
insert into #b(bid,totalCost)values(1,0)
insert into #b(bid,totalCost)values(2,0)
insert into #b(bid,totalCost)values(3,0)

update #b set totalCost=(select sum(unitCost*amount) from #a where aid=bid group by aid)
select * from #b