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