MySQL_采购入库价格与在线售价监控_20161213

c037采购入库价格与在线售价监控

##c037采购入库价格与在线售价监控
SELECT a.城市,a.产品ID,a.商品名称,a.入库日期,a.入库仓库,a.单价,a.总金额,a.采购人,b.单价 AS 上次入库单价,b.总金额 AS 上次总金额
,(a.单价-b.单价)/b.单价 AS 变化幅度1,(a.单价-c.单品售价)/c.单品售价 AS 变化幅度2
,c.单品售价,c.打包售价,DATE(b.入库日期) AS 上次入库日期,b.入库仓库 AS 上次入库仓库
FROM (#添加增长序列号
	SELECT a1.*,@i:=@i+1 AS ID1
	FROM (
		SELECT 城市,产品ID,商品名称,入库日期,入库仓库,单价,总金额,采购人,入库类型
		FROM `a006_stock_in_item`  
		WHERE 入库类型<>'退货' AND 城市='城市A' #AND  产品ID=10327
		ORDER BY 城市,产品ID,入库仓库,入库日期
	)AS a1,(SELECT (@i:=0) ) AS a2
) AS a
LEFT JOIN (#下错一行
	SELECT b1.*,@j:=@j+1 AS ID2
	FROM (
		SELECT 城市,产品ID,商品名称,入库日期,入库仓库,单价,总金额,采购人,入库类型
		FROM `a006_stock_in_item`  
		WHERE  入库类型<>'退货' AND 城市='城市A' #AND  产品ID=10327
		ORDER BY 城市,产品ID,入库仓库,入库日期
	)AS b1,(SELECT (@j:=1) ) AS b2 
) AS b ON a.城市=b.城市 AND a.产品ID=b.产品ID AND a.ID1=b.ID2 AND a.入库仓库=b.入库仓库
LEFT JOIN (#10表在线价格 市场改成仓库
	SELECT 城市,日期,产品ID,`单品售价`,`打包售价`,
	CASE WHEN 市场='城北市场' THEN '城北仓库' WHEN 市场='城南市场' THEN '城南仓库' #城市A
	ELSE NULL END AS 仓库
	FROM `a010_dynamic_产品` 
	WHERE 日期=DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY)
	ORDER BY 城市,产品ID,仓库
) AS c ON a.产品ID=c.产品ID AND a.入库仓库=c.仓库 # and a.城市=c.城市 
WHERE DATE(a.入库日期)=DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AND ABS((a.单价-b.单价)/b.单价)>=0.1 AND  (a.单价-c.单品售价)<0
ORDER BY a.城市,ABS((a.单价-b.单价)/b.单价) DESC