// 大中小级联base
select * from small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code

// 出库找中储位栈板

SELECT distinct b.medium_storage_code FROM small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
left join pallet d
on a.pallet_code = d.pallet_code
where d.material_code = '' and d.material_num <= '' and
(c.large_storage_code = '' or c.large_storage_code = '') and
a.pallet_code is not null and a.outbound_freeze_pallet is null
order by d.inbound_date


// 尾数区找栈板

SELECT a.small_storage_code,d.pallet_code,d.material_code,d.material_num FROM small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
left join pallet d
on a.pallet_code = d.pallet_code
where d.material_code = '' and d.material_num <= '' and
c.large_storage_type = ''  and
a.pallet_code is not null and a.outbound_freeze_pallet is null
order by d.inbound_date


// 出库找中储位里面的栈板信息
SELECT a.small_storage_code,a.is_entrance,a.column_num,a.outbound_freeze_pallet,
b.pallet_code,b.material_code,b.material_num,
b.inbound_order_code,b.batch_code,b.inbound_date
FROM small_storage a
left join pallet b
on a.pallet_code = b.pallet_code
where a.medium_storage_code = 'ww'
order by a.column_num


// 待分拣区找有这个订单，有空位的所有中储位
select a.medium_storage_code from medium_storage a
left join large_storage c
on a.large_storage_code = c.large_storage_code
left join
(
select medium_storage_code, count(*) as pallet_num  from small_storage
where
pallet_code is  null and inbound_freeze_pallet is  null
and outbound_freeze_pallet is  null
) b
on a.medium_storage_code = b.medium_storage_code
where
b.pallet_num is not null and c.large_storage_type = 'STORAGE_AREA_3F'
and a.outbound_order = '' and b.pallet_num > 0
order by c.order_num,a.order_num


// 尾数区各物料数量
select d.material_code , ifnull(count(*),0) as material_num
from small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
left join pallet d
on a.pallet_code = d.pallet_code
where c.large_storage_type = 'STORAGE_AREA_3F'
and a.outbound_freeze_pallet is null
group by d.material_code
having d.material_code = 'sku-10101'


// 尾数区的空储位
select a.small_storage_code from small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
where c.large_storage_type = 'STORAGE_AREA_3F' and
a.pallet_code is null and a.inbound_freeze_pallet is null and
a.outbound_freeze_pallet is null


// 找待分拣区指定订单的中储位
select b.medium_storage_code from medium_storage b
left join large_storage c
on b.large_storage_code = c.large_storage_code
where (c.large_storage_type = 'STORAGE_AREA_3F' or c.large_storage_type = 'STORAGE_AREA_3F')
and b.outbound_order = 'qwe'


// 判断中储位有多少栈板
SELECT count(*) as num FROM small_storage
where medium_storage_code = 'zz' and
(pallet_code is not null or inbound_freeze_pallet is not null
or outbound_freeze_pallet is not null)

// 判断大储位有多少栈板
select count(*) from small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
where c.large_storage_code = 'aa' and
(a.pallet_code is not null or a.inbound_freeze_pallet is not null
or a.outbound_freeze_pallet is not null)

// 小储位属于哪个大储位
select c.large_storage_code from small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
where a.small_storage_code == ''


// 尾数区可用栈板
SELECT  a.small_storage_code,a.pallet_code,d.material_code,d.material_num,c.floor_num,d.inbound_date
FROM small_storage a
left join medium_storage b
on a.medium_storage_code = b.medium_storage_code
left join large_storage c
on b.large_storage_code = c.large_storage_code
left join pallet d
on a.pallet_code = d.pallet_code
where d.material_code = '10359A14D1001' and d.material_num <= 9 and
c.large_storage_type = 'MANTISSA_AREA_5F' and
a.pallet_code is not null and a.outbound_freeze_pallet is null
order by d.inbound_date
limit 1