存儲過程問題(多表關聯更新)

Question

-----------------------------------------------------------
採購主檔:
id
採購單號
採購日期
客戶編號
...採購明細:
id
採購主檔id(用來關聯採購主檔id)
產品編號
品名
規格
數量
已進貨數量
...採購關聯: SELECT * FROM 採購明細 LEFT JOIN 採購主檔 ON 採購明細.採購主檔id=採購主檔.id
----------------------------------------------------------
進貨主檔:
id
進退別(進/退)
進/退貨單號
進/退貨日期
...進貨明細
進/退貨主檔id(用來關聯進/退貨主檔id)
採購單號
產品編號
品名
規格
數量進貨關聯: SELECT * FROM 進貨明細 LEFT JOIN 進貨主檔 ON 進貨明細.進貨主檔id=進貨主檔.id
-----------------------------------------------------------如何根據[進貨主檔[的[進/退別],及[進貨明細檔]的[採購單號]及[產品編號],更新[採購明細檔]的[已進貨數量]寫一存儲過程(SQL Server)

Answer 1

create proc sp_test
as
UPDATE a set 已进货数量 = b.已进货数量
FROM 采购明细表 a,
(select 采购单号,产品编号,sum(数量) as 已进货数量 from 进货明细表 where 进退别='进' group by 采购单号,产品编号) b
WHERE a.采购主档ID = b.采购单号 and a.产品编号 = b.产品编号
go

Answer 2

WHERE  a.采购主档ID  =  b.采购单号  and  a.产品编号  =  b.产品编号 a.采购主档ID  =  b.采购单号  並不成立
 
因採購明細與採購主檔的關聯為: 採購明細檔.採購主檔id=採購主檔.id

Answer 3

抱歉,上面的代码的确有问题,进退别字段不是进货明细表中的,而是进货主档表中的,而且表关联有问题.改为:
UPDATE a set 已进货数量 = b.已进货数量
FROM 采购明细表 a,
(select c.采购单号,c.产品编号,sum(c.数量) as 已进货数量 
from 进货明细表 c inner join 进货主档 d on c.进货主檔id = d.id 
where d.进退别='进' 
group by c.采购单号,c.产品编号
) b,
采购主档 e
WHERE a.采购主档ID = e.id and e.采购单号 = b.采购单号 and a.产品编号 = b.产品编号
--------------------------------------------------------------------------------
请楼主先测试一下.

Answer 4

UPDATE  a  set  yjhsl  =  b.yjhsl
FROM  cgmx  a,  
(select cgzd.id, jthmx.cpbh, sum(jthmx.sl)  as yjhsl  from jthmx left join jthzd on jthmx.jthzdid=jthzd.id left join cgzd on jthmx.cgdh=cgzd.cgdh
where  jtb='進'  group  by jthmx.cgdh, jthmx.cpbh, cgzd.id)  b  
WHERE  a.cgzdid  =  b.id  and  a.cpbh  =  b.cpbh謝謝你hellowork, 我早上根據你的思路改了一下. 現在是進/退別的問題了.如何處理進退別呢?
如果有進退別好像不能用SUN, 但用SUN對數據來說是最安全的(即使前一次有更新錯誤了. 后一次的計算也會把他修正過來).我是想將其寫成觸發器.請指教.

Answer 5

触发器问题不大.
在进货明细表的insert触发器中使用
采购明细.已进货数量=进货明细.数量+采购明细.已进货数量.
在进货明细表的update触发器中使用
采购明细.已进货数量=更新后进货明细.数量-更新前进货明细.数量+采购明细.已进货数量
如果需要,还可以考虑在delete触发器中进行动态处理.
楼主所说的:
----------------------------------------------------------------------------------
現在是進/退別的問題了.如何處理進退別呢?
----------------------------------------------------------------------------------
是什么意思呢?是不是退货时要从采购明细.已进货数量中减掉?请楼主说明一下.

Answer 6

----综合汇总.在创建触发器前执行此汇总,相当于初始化.然后触发器在汇总好的数据上进行动态更新.
UPDATE a set yjhsl = b.yjhsl
FROM cgmx a,
(select cgzd.id, jthmx.cpbh, 
sum(case jthzd.jtb when '进' then jthmx.sl else -jthmx.sl end) as yjhsl  --此处根据[进退货主档.进退别]计算总进货数量.若是进货则加,若是退货则减.
from jthmx left join jthzd on jthmx.jthzdid=jthzd.id left join cgzd on jthmx.cgdh=cgzd.cgdh  
group  by jthmx.cgdh, jthmx.cpbh, cgzd.id)  b 
WHERE  a.cgzdid  =  b.id  and  a.cpbh  =  b.cpbh
----[进退货明细表]INSERT触发器
create trigger tri_insert on jthmx
for insert
as
UPDATE a set yjhsl = yjhsl + b.yjhsl
FROM cgmx a,
(select cgzd.id, i.cpbh, 
sum(case jthzd.jtb when '进' then i.sl else -i.sl end) as yjhsl 
from inserted i left join jthzd on i.jthzdid=jthzd.id left join cgzd on i.cgdh=cgzd.cgdh  
group  by i.cgdh, i.cpbh, cgzd.id)  b 
WHERE  a.cgzdid  =  b.id  and  a.cpbh  =  b.cpbh 
go
----[进退货明细表]UPDATE触发器
create trigger tri_update on jthmx
for update
as
UPDATE a set yjhsl = yjhsl + b.yjhsl
FROM cgmx a,
(select cgzd.id, d.cpbh, 
sum(case jthzd.jtb when '进' then (i.sl - d.sl) else -(i.sl - d.sl) end) as yjhsl 
from deleted d left join  inserted i on d.jthzdid = i.jthzdid  left join jthzd on d.jthzdid=jthzd.id left join cgzd on d.cgdh=cgzd.cgdh  
group  by d.cgdh, d.cpbh, cgzd.id)  b 
WHERE  a.cgzdid  =  b.id  and  a.cpbh  =  b.cpbh 
go
----[进退货明细表]DELETE触发器
create trigger tri_delete on jthmx
for delete
as
UPDATE a set yjhsl = yjhsl - b.yjhsl
FROM cgmx a,
(select cgzd.id, d.cpbh, 
sum(case jthzd.jtb when '进' then d.sl else -d.sl end) as yjhsl 
from deleted d left join jthzd on d.jthzdid=jthzd.id left join cgzd on d.cgdh=cgzd.cgdh  
group  by d.cgdh, d.cpbh, cgzd.id)  b 
WHERE  a.cgzdid  =  b.id  and  a.cpbh  =  b.cpbh
go
----注意:如果[进退货主档表]要修改[进退别]或要删除,还要为[进退货主档表]分别创建update和delete触发器,在触发器中判断进退别是否被更改,若更改则更新[采购明细表]中的[已进货数量]

Answer 7

謝謝你,hellowork,
按照您的方法, 該問題已經OK.

Answer 8

hellowork
我剛才又測試了一次. for update不對.當我改變cpbh時. 沒有對應該刪除前一產品的數量和增加新cpbh的數量