MySQL Learning _ View the sales of the warehouse products _20161102

The Order table structure is specific to each order under multiple products, while the warehouse shipping table structure is the sum of the amounts for each order is not differentiated by the order product

So if you want to calculate the sales of each product in each warehouse, you need to connect two tables and the product is online and in stock yesterday.

#昨日在线且有库存的SKUSELECTA. City, a. Warehouse, B.*,SUM(A. Order amount) asHistory 15 days amount,SUM(A. Sales) ashistory 15 days sales,SUM(IF(A. Order Date>=Date_add (current_date, INTERVAL- 7  Day) andA. Order Date<current_date, A. Order Amount,NULL)) asHistory 7 days amount,SUM(IF(A. Order Date>=Date_add (current_date, INTERVAL- 7  Day) andA. Order Date<current_date, A. Sales,NULL)) asHistory 7 Days sales,SUM(IF(A. Order Date>=Date_add (current_date, INTERVAL- 3  Day) andA. Order Date<current_date, A. Order Amount,NULL)) asHistory 3 days amount,SUM(IF(A. Order Date>=Date_add (current_date, INTERVAL- 3  Day) andA. Order Date<current_date, A. Sales,NULL)) asHistory 3 Days Sales from(#以订单ID匹配仓库 specific to each product ID for each orderSELECTA1. City, A2. Warehouse, date (A1. Order date) asOrder date, A1. Order ID,A1. Product ID,SUM(A1. Amount) asOrder Amount,SUM(Total quantity of items) asSales from' A003_order ' asA1INNER JOIN' A018_cgwy_order_car ' asA2 onA1. Order ID=A2. Order NumberWHEREA1. City=Beijing andA1. Amount>0  andA1. Order Date>=Date_add (current_date, INTERVAL-  the  Day) andA1. Order Date<current_date     GROUP  byA1. Order ID,A1. Product ID) asaINNER JOIN(#昨日在线且昨日有库存的产品SELECTB1. Product id,b3. Commodity name, B3. Unit, B1. Price, B3. Conversion rate, B3. Packing unit, B1. Packaging price, B3. Brand, B3. Total inventory, B3. Inventory, B3. Inventory average Cost, B3. Total inventory, B3. On-line and in stock  from' A010_dynamic_sku ' asB1INNER JOIN (        SELECTB2. City, B2. Product id,b2. Commodity name, B2. Brand, B2. Total inventory, B2. Inventory packing, B2. Inventory average cost, b2. Unit, B2. Packing unit, B2. Conversion rate, date (B2. Dates) asonline and in stock date from' A014_stock_daily ' asB2WHEREB2. City=Beijing andDate (B2. Dates)=Date_add (current_date, INTERVAL- 1  Day) andB2. Total inventory>0  andB2. Product ID= the        GROUP  byB2. Product ID) asB3 onB3. Product ID=B1. Product IDWHEREB1. City=Beijing andDate (B1. Online date)=Date_add (current_date, INTERVAL- 1  Day)    GROUP  byB1. Product ID) asB onA. Product ID=B. Product IDGROUP  byA. City, A. Warehouse, a. Product IDORDER  byA. Product ID

MySQL Learning _ View the sales of the warehouse products _20161102