统计主要供应商供货数量和金额的比例一

来源：互联网发布：侠盗飞车mac版下载编辑：程序博客网时间：2024/04/30 12:10

如何通过金蝶分析一段时间（主要）各供应商供货数量和金额占（主要）供应商总数量和总金额的比例。

select distinct
(Select sum(fauxqty) from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 INNER JOIN t_Supplier t4 ON     v1.FSupplyID = t4.FItemID   AND t4.FItemID <>0
where 1=1 AND ( t4.FNumber = 'A供应商代码' or t4.FNumber = 'B供应商代码' or t4.FNumber = 'C供应商代码'  )
AND (v1.Fdate >= '********' AND v1.Fdate <= '########' )
AND (v1.FTranType=1 AND (v1.FCancellation = 0))) as 总数量,
---如果还需要增加其他供应商可以在后面增加or t4.FNumber = 'D供应商代码',这句是计算这几个供应商供货数量的总和

(Select sum(fauxqty) from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 INNER JOIN t_Supplier t4 ON     v1.FSupplyID = t4.FItemID   AND t4.FItemID <>0
where 1=1 AND t4.FNumber = 'A供应商代码' AND (v1.Fdate >= '********' AND v1.Fdate <= '########'
) AND (v1.FTranType=1 AND (v1.FCancellation = 0))) as A供应商数量总和,
--单独计算A供应商供货数量
(Select sum(fauxqty) from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 INNER JOIN t_Supplier t4 ON     v1.FSupplyID = t4.FItemID   AND t4.FItemID <>0
where 1=1 AND t4.FNumber = 'A供应商代码' AND (v1.Fdate >= '********' AND v1.Fdate <= '########' )
AND (v1.FTranType=1 AND (v1.FCancellation = 0)))/(Select sum(fauxqty) from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 INNER JOIN t_Supplier t4 ON
v1.FSupplyID = t4.FItemID   AND t4.FItemID <>0 where 1=1 AND ( t4.FNumber = 'A供应商代码' or t4.FNumber = 'B供应商代码' or t4.FNumber = 'C供应商代码')
AND (v1.Fdate >= '********' AND v1.Fdate <= '########' ) AND (v1.FTranType=1 AND (v1.FCancellation = 0)) ) as A供应商数量百分比,

--A供应商数量总和除以总数量,就是上面第二段select 语句除第一段select语句得出了百分比，其他供应商计算依次类推

from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON v1.FInterID = u1.FInterID AND u1.FInterID <>0 INNER JOIN t_Supplier t4 ON v1.FSupplyID = t4.FItemID AND t4.FItemID <>0
where 1=1 AND ( t4.FNumber = 'A供应商代码' or t4.FNumber = 'B供应商代码' or t4.FNumber = 'C供应商代码' )
AND (v1.Fdate >= '********' AND v1.Fdate <= '########' ) AND (v1.FTranType=1 AND (v1.FCancellation = 0))
--如果需要计算金额，可以把语句的fauxqty全部替换成famount,这种方法比较简单和直接，但是语句比较长（供应商多的话）.