[步骤]
SELECT swp.id,swp.position_code,
swp.volume,
IFNULL(swp.occupy_volume,0) occupy_volume,
IFNULL(swp.pre_occupy_volume,0) pre_occupy_volume,
wpa.warehouse_area_id,
wa.area_name,
w.ware_house_name
FROM t_stk_warehouse_position swp
LEFT JOIN t_stk_warehouse_post_area wpa ON wpa.position_id=swp.id and wpa.customer_id=swp.customer_id
LEFT JOIN rb_ware_house w on w.id=swp.warehouse_id and w.customer_id=swp.customer_id
LEFT JOIN t_stk_warehouse_area wa ON wa.id=wpa.warehouse_area_id AND wa.customer_id=wpa.customer_id
LEFT JOIN (
select COUNT(DISTINCT order_no) inventoryCount,position_id,warehouse_id,customer_id FROM t_stk_inventory_box_labels
WHERE warehouse_id=16752 and customer_id=1 AND inventory>0
GROUP BY position_id,warehouse_id,customer_id
) c on c.position_id=swp.id AND c.warehouse_id=swp.warehouse_id AND c.customer_id=swp.customer_id
LEFT JOIN (
select COUNT(DISTINCT pr.order_code) preCount,pr.recommend_position_id,pr.ware_house_id,pr.customer_id FROM t_stk_position_recommend_record pr
WHERE pr.ware_house_id=16752 and pr.customer_id=1 and IFNULL(pr.is_deleted,0)=0 AND NOT EXISTS(
select 1 from t_stk_inventory_box_labels WHERE warehouse_id=pr.ware_house_id AND position_id=pr.recommend_position_id
AND customer_id=pr.customer_id AND order_no=pr.order_code AND inventory>0
)
GROUP BY pr.recommend_position_id,pr.ware_house_id,pr.customer_id
)d on d.recommend_position_id=swp.id AND d.ware_house_id=swp.warehouse_id AND d.customer_id=swp.customer_id
WHERE swp.customer_id=1 AND swp.warehouse_id=16752 and swp.is_active=1
AND swp.position_state=1
AND wpa.warehouse_area_id=590
AND swp.position_type=4
AND swp.limit_order_count-IFNULL(c.inventoryCount,0)-IFNULL(d.preCount,0)>0
AND IFNULL(swp.volume,0)*0.9000-IFNULL(swp.occupy_volume,0)-IFNULL(swp.pre_occupy_volume,0)>=23232.000000000000
AND wa.is_active_recommend=1
ORDER BY IFNULL(swp.volume,0)-IFNULL(swp.occupy_volume ,0)-IFNULL(swp.pre_occupy_volume,0),position_code
LIMIT 1
[结果]
[期望]
修改 SQL 语句 结构
SELECT
swp.id,
swp.position_code,
swp.volume,
IFNULL(swp.occupy_volume, 0) AS occupy_volume,
IFNULL(swp.pre_occupy_volume, 0) AS pre_occupy_volume,
wpa.warehouse_area_id,
wa.area_name,
w.ware_house_name
FROM t_stk_warehouse_position swp
INNER JOIN t_stk_warehouse_post_area wpa
ON wpa.position_id = swp.id AND wpa.customer_id = swp.customer_id
INNER JOIN rb_ware_house w
ON w.id = swp.warehouse_id AND w.customer_id = swp.customer_id
INNER JOIN t_stk_warehouse_area wa
ON wa.id = wpa.warehouse_area_id AND wa.customer_id = wpa.customer_id
LEFT JOIN LATERAL (
SELECT COUNT(DISTINCT order_no) AS inventoryCount
FROM t_stk_inventory_box_labels
WHERE warehouse_id = swp.warehouse_id
AND customer_id = swp.customer_id
AND position_id = swp.id
AND inventory > 0
) c ON TRUE
LEFT JOIN LATERAL (
SELECT COUNT(DISTINCT pr.order_code) AS preCount
FROM t_stk_position_recommend_record pr
WHERE pr.ware_house_id = swp.warehouse_id
AND pr.customer_id = swp.customer_id
AND pr.recommend_position_id = swp.id
AND IFNULL(pr.is_deleted, 0) = 0
AND NOT EXISTS (
SELECT 1 FROM t_stk_inventory_box_labels ibl
WHERE ibl.warehouse_id = pr.ware_house_id
AND ibl.position_id = pr.recommend_position_id
AND ibl.customer_id = pr.customer_id
AND ibl.order_no = pr.order_code
AND ibl.inventory > 0
)
) d ON TRUE
WHERE swp.customer_id = 1
AND swp.warehouse_id = 16752
AND swp.is_active = 1
AND swp.position_state = 1
AND wpa.warehouse_area_id = 590
AND swp.position_type = 4
AND wa.is_active_recommend = 1
AND swp.limit_order_count - IFNULL(c.inventoryCount, 0) - IFNULL(d.preCount, 0) > 0
AND IFNULL(swp.volume, 0) * 0.9 - IFNULL(swp.occupy_volume, 0) - IFNULL(swp.pre_occupy_volume, 0) >= 23232
ORDER BY IFNULL(swp.volume, 0) - IFNULL(swp.occupy_volume, 0) - IFNULL(swp.pre_occupy_volume, 0), position_code
LIMIT 1;
秒出