v_sumpayment.sql

Visual Basic + Oracle 9i数据库应用系统开发与实例（企业购销存管理系统）界面简洁,数据库完整,还不错~适合学生利用和使用!

--统计付款信息
CREATE OR REPLACE VIEW V_SUMPAYMENT AS
--从采购单中提取数据,字段包括采购单编号、采购类型、客户名、产品名
--应付总金额、实付总金额、需付金额
SELECT o.OrderId,o.ordertype,c.clientname,pd.proname,
--使用decode()函数把null值替换为0
decode(o.proamount,null,0,o.proamount) as orderPay,
decode(sum(p.amount),null,0,sum(p.amount)) as RealPay,
SUM(decode(o.proamount,null,0,o.proamount)) - SUM(decode(p.amount,null,0,p.amount)) AS SumAmount
--从表Orders、表Client、表Product、表Payment中读取数据
FROM OSSMAN.Orders o, OSSMAN.Client c,OSSMAN.Product pd, OSSMAN.Payment p
WHERE pd.proid=o.proid and o.OrderId = p.OriId(+) and o.ordertype='采购' and o.clientid=c.clientid
--进行分组统计
GROUP BY o.OrderId, o.ordertype,o.proamount,c.clientname,pd.proname
--联合查询，把两个查询的结果集合并
union 
--从销售单中提取数据，字段包括销售单编号、销售类型、客户名、产品名
--应付总金额、实付总金额、需付金额
SELECT s.saleid, s.saletype,c.clientname,pd.proname,
decode(s.proamount,null,0,s.proamount) as orderPay,
decode(sum(p.amount),null,0,sum(p.amount)) as RealPay,
SUM(decode(s.proamount,null,0,s.proamount)) - SUM(decode(p.amount,null,0,p.amount)) AS SumAmount
--从表Sales、表Client、表Product和表Payment中读取数据
FROM OSSMAN.Sales s,OSSMAN.Client c,OSSMAN.Product pd, OSSMAN.payment p
WHERE s.proid=pd.proid and s.saleid = p.oriid(+) and s.saletype='销售退货' and s.clientid=c.clientid
GROUP BY s.saleid ,s.saletype,s.proamount,c.clientname,pd.proname