mysql 部分计算调拨逻辑

select t4.sp码,
t4.zaitu as '在途',
t4.仓库名称 AS '中心仓',
t4.标品名称,
t4.库存成本,
t4.货主,
t4.分类名称,
t4.一级分类名称,
t4.库存数量 as '中心仓库存',
t4.前置仓,
t4.城市,
IFNULL(t1.有库存天数, 0) AS '有库存天数',
IFNULL(t2.销量, 0) AS '销量',
IFNULL(t2.销售天数, 0) AS '销售天数',
IFNULL(t_14.近12日销量, 0) AS '近12日销量'
from(
select t.sp码, ifnull((xtw.库存数量+ xtw.wms在途数量+ xtw.wms验收中数量), 0) as zaitu, t.仓库名称, t.标品名称, t.库存成本, t.货主, t.分类名称, t.一级分类名称, t.库存数量, xtwr.前置仓, xtwr.城市
from(
SELECT 仓库名称, 标品名称, sp码, 货主, 分类名称, 一级分类名称, 库存数量, 库存成本
FROM xtw_stock xtws
where 仓库名称 like "%中心仓%" and 库存数量> 0) t
left JOIN xtw_relation xtwr
on t.仓库名称= xtwr.配属中心仓
left join xtw_stock xtw on xtwr.前置仓= xtw.仓库名称
and t.sp码= xtw.sp码 and t.货主= xtw.货主) t4

-- 库存情况
left join(
SELECT 仓库, sp_code, COUNT(DISTINCT pt(day)) as '有库存天数'
FROM stock
where date_format(pt(day), '%Y-%m-%d')> date_sub(curdate(), interval 30 day)
AND date_format(pt(day), '%Y-%m-%d')< date_sub(curdate(), interval 0 day)
GROUP BY 仓库, sp_code) t1
on t1.仓库= t4.前置仓and t1.sp_code= t4.sp码

-- 销售情况
left join
(
SELECT 仓库, sp_code, SUM(货值) AS '销售货值', sum(数量) AS '销量', count(DISTINCT pt(day)) as '销售天数'
FROM SALE
where date_format(pt(day), '%Y-%m-%d')>= date_sub(curdate(), interval 30 day)
AND date_format(pt(day), '%Y-%m-%d')< date_sub(curdate(), interval 0 day)
GROUP BY 仓库, sp_code) t2
on t2.仓库= t4.前置仓and t2.sp_code= t4.sp码

-- 近14日销量
LEFT JOIN
(
SELECT 仓库, sp_code, sum(数量) AS '近12日销量'
FROM SALE
where date_format(pt(day), '%Y-%m-%d')>= date_sub(curdate(), interval 12 day)
AND date_format(pt(day), '%Y-%m-%d')< date_sub(curdate(), interval 0 day)
group by 仓库, sp_code) t_14
on t_14.仓库= t4.前置仓and t_14 .sp_code= t4.sp码