v_sumincome.sql

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

--统计收款信息
CREATE OR REPLACE VIEW V_SUMINCOME AS
--从采购单中提取数据,字段包括采购单编号、采购类型、客户名、产品名
--应收总金额、实收总金额、需付金额
SELECT o.OrderId, o.ordertype,c.clientname,pd.proname,
--使用decode()函数把null值替换为0
decode(o.proamount,null,0,o.proamount) as orderPay,
decode(sum(i.amount),null,0,sum(i.amount)) as RealPay,
SUM(decode(o.proamount,null,0,o.proamount)) - SUM(decode(i.amount,null,0,i.amount)) AS SumAmount
--从表Orders、表Client、表Product、表Income中读取数据
FROM OSSMAN.Orders o, OSSMAN.Client c,OSSMAN.Product pd,OSSMAN.Income i
WHERE pd.proid=o.proid and o.OrderId = i.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(i.amount),null,0,sum(i.amount)) as RealPay,
SUM(decode(s.proamount,null,0,s.proamount)) - SUM(decode(i.amount,null,0,i.amount)) AS SumAmount
--从表Sales、表Client、表Product和表Income中读取数据
FROM OSSMAN.Sales s,OSSMAN.Client c,OSSMAN.Product pd, OSSMAN.Income i
WHERE pd.proid=s.proid and s.saleid = i.oriid(+) and s.saletype='销售' and s.clientid=c.clientid
--进行分组统计
GROUP BY s.saletype,s.saleid,s.proamount,c.clientname,pd.proname