MySQL如何計算動銷率_20161025

標籤：rom   orm   它的   format   and   span   品種   date   city   

動銷率一般反映在採購管理上,它的公式為：商品動銷率=（動銷品種數 /倉庫總品種數）*100% 。

也可以理解為銷售的商品數量和倉庫庫存的商品數量，假如你倉庫裡有100個品種，在上月銷售了50種，動銷率=50/100*100%=50%

這裡我們有兩個表,一個是庫存表一個是訂單銷售表。

SELECT c.*,d.庫存商品數,c.銷售商品數/d.庫存商品數 AS 動銷率FROM (#各城市各月銷售商品數    SELECT b.城市,b.年月,COUNT(b.商品ID) AS 銷售商品數    FROM (        SELECT a.city AS 城市,a.`productID` AS 商品ID,DATE_FORMAT(a.`order_date`,"%Y%m") AS 年月        FROM `test_a03order` AS a#訂單表        GROUP BY a.city ,a.`productID` ,DATE_FORMAT(a.`order_date`,"%Y%m")    ) AS b    GROUP BY b.城市,b.年月) AS cLEFT JOIN (#各城市各月庫存商品數    SELECT b.城市,b.年月,COUNT(b.商品ID) AS 銷售商品數    FROM (        SELECT a.city AS 城市,a.`productID` AS 商品ID,DATE_FORMAT(a.`date`,"%Y%m") AS 年月        FROM `test_a05order` AS a#庫存表        GROUP BY a.city ,a.`productID` ,DATE_FORMAT(a.`date`,"%Y%m")    ) AS b    GROUP BY b.城市,b.年月) AS d ON c.城市=d.城市 AND c.年月=d.年月

 

MySQL如何計算動銷率_20161025