MySQL學習_查看各倉庫產品的銷售情況_20161102

標籤：toc   產品   商品   mic   單表   線上   color   date   join   

訂單表結構是具體到每個訂單下面多個產品，而倉庫出貨的表結構是對每個訂單的金額匯總 不區分訂單產品 

因此如果想計算每個倉庫每個產品的銷售情況 需要將兩個表串連起來 並且產品是昨天線上且有庫存的產品

#昨日線上且有庫存的SKUSELECT a.城市,a.倉庫,b.*,SUM(a.訂單額) AS 曆史15天金額,SUM(a.銷量) AS 曆史15天銷量,SUM(IF(a.訂單日期>=DATE_ADD(CURRENT_DATE,INTERVAL - 7 DAY) AND a.訂單日期<CURRENT_DATE,a.訂單額,NULL)) AS 曆史7天金額,SUM(IF(a.訂單日期>=DATE_ADD(CURRENT_DATE,INTERVAL - 7 DAY) AND a.訂單日期<CURRENT_DATE,a.銷量,NULL)) AS 曆史7天銷量,SUM(IF(a.訂單日期>=DATE_ADD(CURRENT_DATE,INTERVAL - 3 DAY) AND a.訂單日期<CURRENT_DATE,a.訂單額,NULL)) AS 曆史3天金額,SUM(IF(a.訂單日期>=DATE_ADD(CURRENT_DATE,INTERVAL - 3 DAY) AND a.訂單日期<CURRENT_DATE,a.銷量,NULL)) AS 曆史3天銷量FROM (#以訂單ID匹配倉庫 具體到每筆訂單每個產品ID    SELECT a1.城市,a2.倉庫,DATE(a1.訂單日期) AS 訂單日期,a1.訂單ID,a1.產品ID,SUM(a1.金額) AS 訂單額,SUM(匯總單品量) AS 銷量    FROM `a003_order` AS a1     INNER JOIN `a018_cgwy_order_car` AS a2 ON a1.訂單ID=a2.訂單號    WHERE a1.城市="北京" AND a1.金額>0 AND a1.訂單日期>=DATE_ADD(CURRENT_DATE,INTERVAL - 15 DAY) AND  a1.訂單日期<CURRENT_DATE     GROUP BY a1.訂單ID,a1.產品ID) AS aINNER JOIN (#昨日線上且昨日有庫存的產品    SELECT b1.產品ID,b3.商品名稱,b3.單位,b1.單品售價,b3.轉換率,b3.打包單位,b1.打包售價,b3.品牌,b3.庫存總數,b3.庫存打包數,b3.庫存平均成本,b3.庫存總額,b3.線上且有庫存日期    FROM  `a010_dynamic_sku` AS b1    INNER JOIN (        SELECT b2.城市,b2.產品ID,b2.商品名稱,b2.品牌,b2.庫存總數,b2.庫存打包數,b2.庫存平均成本,b2.庫存總額,b2.單位,b2.打包單位,b2.轉換率,DATE(b2.日期) AS 線上且有庫存日期        FROM  `a014_stock_daily` AS b2        WHERE b2.城市="北京" AND DATE(b2.日期)=DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY) AND b2.庫存總數>0 AND b2.產品ID=42        GROUP BY b2.產品ID    ) AS b3 ON b3.產品ID=b1.產品ID    WHERE b1.城市="北京" AND DATE(b1.線上日期)=DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY)    GROUP BY b1.產品ID) AS b ON a.產品ID=b.產品IDGROUP BY a.城市,a.倉庫,a.產品IDORDER BY a.產品ID

 

MySQL學習_查看各倉庫產品的銷售情況_20161102