反馈人：袁慧斌
数据库：zhcxkj-center-warehouse
查询时间：67.395
扫描行数：951.857
发送行数：162
指纹SQL: SELECT main . WarehouseID , K . stage Stage , main . MoveType , main . NumType , MAX ( CASE WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( A . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( A . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( A . TaskNum , ? ) WHEN stage = '?' THEN ? WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( D . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( D . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( D . TaskNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( F . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( F . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( F . TaskNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( H . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( H . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( H . TaskNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( J . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( J . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( J . TaskNum , ? ) END ) NotComplelete , MAX ( CASE WHEN stage = '?' THEN ? WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( B . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( B . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( B . TaskNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( C . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( C . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( C . TaskNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( E . ProductNumber , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( E . SKUNum , ? ) WHEN stage = '?' AND main . NumType = '?' THEN IFNULL ( E . TaskNum , ? ) WHEN stage
真实SQL：SELECT main.WarehouseID ,K.stage Stage ,main.MoveType ,main.NumType ,MAX( CASE WHEN stage='移库待生成' AND main.NumType='产品数量' THEN IFNULL(A.ProductNumber,0) WHEN stage='移库待生成' AND main.NumType='SKU数' THEN IFNULL(A.SKUNum,0) WHEN stage='移库待生成' AND main.NumType='任务数' THEN IFNULL(A.TaskNum,0) WHEN stage='移库生成' THEN 0 WHEN stage='移库打印' AND main.NumType='产品数量' THEN IFNULL(D.ProductNumber,0) WHEN stage='移库打印' AND main.NumType='SKU数' THEN IFNULL(D.SKUNum,0) WHEN stage='移库打印' AND main.NumType='任务数' THEN IFNULL(D.TaskNum,0) WHEN stage='移库拣货' AND main.NumType='产品数量' THEN IFNULL(F.ProductNumber,0) WHEN stage='移库拣货' AND main.NumType='SKU数' THEN IFNULL(F.SKUNum,0) WHEN stage='移库拣货' AND main.NumType='任务数' THEN IFNULL(F.TaskNum,0) WHEN stage='移库分拣' AND main.NumType='产品数量' THEN IFNULL(H.ProductNumber,0) WHEN stage='移库分拣' AND main.NumType='SKU数' THEN IFNULL(H.SKUNum,0) WHEN stage='移库分拣' AND main.NumType='任务数' THEN IFNULL(H.TaskNum,0) WHEN stage='移库入库' AND main.NumType='产品数量' THEN IFNULL(J.ProductNumber,0) WHEN stage='移库入库' AND main.NumType='SKU数' THEN IFNULL(J.SKUNum,0) WHEN stage='移库入库' AND main.NumType='任务数' THEN IFNULL(J.TaskNum,0) END) NotComplelete ,MAX( CASE WHEN stage='移库待生成' THEN 0 WHEN stage='移库生成' AND main.NumType='产品数量' THEN IFNULL(B.ProductNumber,0) WHEN stage='移库生成' AND main.NumType='SKU数' THEN IFNULL(B.SKUNum,0) WHEN stage='移库生成' AND main.NumType='任务数' THEN IFNULL(B.TaskNum,0) WHEN stage='移库打印' AND main.NumType='产品数量' THEN IFNULL(C.ProductNumber,0) WHEN stage='移库打印' AND main.NumType='SKU数' THEN IFNULL(C.SKUNum,0) WHEN stage='移库打印' AND main.NumType='任务数' THEN IFNULL(C.TaskNum,0) WHEN stage='移库拣货' AND main.NumType='产品数量' THEN IFNULL(E.ProductNumber,0) WHEN stage='移库拣货' AND main.NumType='SKU数' THEN IFNULL(E.SKUNum,0) WHEN stage='移库拣货' AND main.NumType='任务数' THEN IFNULL(E.TaskNum,0) WHEN stage='移库分拣' AND main.NumType='产品数量' THEN IFNULL(G.ProductNumber,0) WHEN stage='移库分拣' AND main.NumType='SKU数' THEN IFNULL(G.SKUNum,0) WHEN stage='移库分拣' AND main.NumType='任务数' THEN IFNULL(G.TaskNum,0) WHEN stage='移库入库' AND main.NumType='产品数量' THEN IFNULL(I.ProductNumber,0) WHEN stage='移库入库' AND main.NumType='SKU数' THEN IFNULL(I.SKUNum,0) WHEN stage='移库入库' AND main.NumType='任务数' THEN IFNULL(I.TaskNum,0) END) Compleleted ,CURRENT_TIMESTAMP ETLTime FROM ( SELECT MoveType,WarehouseID,NumType FROM(SELECT '存储移订单' MoveType UNION ALL SELECT '订单移存储' MoveType UNION ALL SELECT '订单移订单' MoveType) A CROSS JOIN (SELECT warehouse_id WarehouseID FROM t_stk_order_info WHERE order_state IN (0,1) AND customer_id=1 AND order_status=0 AND is_stock_order = 1 AND warehouse_id IS NOT NULL UNION SELECT warehouse_id WarehouseID FROM t_stk_moving_task WHERE customer_id=1 AND task_type IN (1,2,3,4) AND warehouse_id IS NOT NULL UNION SELECT ware_house_id WarehouseID FROM t_stk_moving_putin_task WHERE customer_id=1 AND shelf_type IN (2,3,5,6) AND ware_house_id IS NOT NULL) B CROSS JOIN ( SELECT '产品数量' NumType UNION ALL SELECT 'SKU数' NumType UNION ALL SELECT '任务数' NumType ) C ) main LEFT JOIN ( -- 移库待生成（待完成） SELECT warehouse_id WarehouseID,'订单移存储' MoveType,COUNT(DISTINCT a.order_code) TaskNum,COUNT(DISTINCT CONCAT(a.order_code,b.SKU)) SKUNum,SUM(b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0)) ProductNumber FROM (SELECT order_code,order_source_id,transport_id,warehouse_id FROM t_stk_order_info WHERE order_state IN (0,1) AND customer_id=1 AND order_status=0 AND is_stock_order = 1) a JOIN (SELECT order_code,id,pending_move_number,lock_num,put_in_num,SKU FROM t_stk_order_list WHERE customer_id=1 AND purchase_type < 2 AND lock_num > 0 AND product_num + IFNULL(product_num_update,0)>0) b ON a.order_code = b.order_code JOIN (SELECT id FROM rb_order_source WHERE customer_id=1) d ON a.order_source_id = d.id LEFT JOIN ( SELECT a.order_code,b.order_list_id, SUM(case when a.task_status<4 then b.sku_number else 0 end) as sumNumber FROM (SELECT order_code,task_status,id FROM t_stk_moving_task WHERE customer_id=1 and task_type in(1,2)) a JOIN (SELECT sku_number,order_list_id,moving_task_id FROM t_stk_moving_task_detail WHERE customer_id=1) b on b.moving_task_id=a.id GROUP BY a.order_code,b.order_list_id )f on f.order_code=a.order_code and f.order_list_id=b.id LEFT JOIN ( SELECT b.order_list_id,SUM(IFNULL(b.moved_number,0)) sumShelfNumber FROM (SELECT id FROM t_stk_moving_task where task_type=3 AND customer_id=1) a join (SELECT moving_task_id,order_list_id,moved_number FROM t_stk_moving_task_detail WHERE customer_id=1) b on a.id=b.moving_task_id GROUP by b.order_list_id )g on g.order_list_id=b.id LEFT JOIN (SELECT order_code FROM t_stk_order_info_beikuexpand WHERE customer_id=1) e ON a.order_code = e.order_code Left join (SELECT Id FROM t_stk_transport WHERE customer_id=1) ts on a.transport_id=ts.Id left join ( SELECT order_code,COUNT(id) waitMovingOrderCount from rb_waitingmoving_orderdetail WHERE IFNULL(waiting_move_state,0)=0 GROUP BY order_code )k ON k.order_code=a.order_code WHERE IFNULL(f.sumNumber,0)>=0 AND IFNULL(k.waitMovingOrderCount,0)=0 AND IFNULL(b.pending_move_number,0)>=IFNULL(g.sumShelfNumber,0) AND b.lock_num -IFNULL(b.pending_move_number,0)-IFNULL(f.sumNumber,0)-b.put_in_num+IFNULL(g.sumShelfNumber,0) > 0 GROUP BY warehouse_id ) A ON main.MoveType=A.MoveType AND main.WareHouseID=A.WareHouseID LEFT JOIN ( -- 移库生成 （已完成） SELECT wareHouse_id WareHouseID,MoveType,COUNT(DISTINCT task_no) TaskNum,COUNT(DISTINCT CONCAT(CASE WHEN IFNULL(b.order_code,'')='' THEN origin_order_code ELSE b.order_code END,b.SKU)) SKUNum,SUM(sku_number) ProductNumber FROM (SELECT wareHouse_id,id,origin_order_code,CASE task_type WHEN 1 THEN '存储移订单' WHEN 2 THEN '存储移订单' WHEN 3 THEN '订单移存储' WHEN 4 THEN '订单移订单' END MoveType FROM t_stk_moving_task WHERE DATE(create_time)=CURRENT_DATE AND customer_id=1 AND task_type IN (1,2,3,4)) a JOIN (SELECT moving_task_id,sku_number,sku,order_code,task_no FROM t_stk_moving_task_detail WHERE customer_id=1) b ON b.moving_task_id=a.id GROUP BY wareHouse_id,MoveType ) B ON main.MoveType=B.MoveType AND main.WareHouseID=B.WareHouseID LEFT JOIN ( -- 移库打印 （已完成） SELECT a.WareHouseID,a.MoveType,TaskNum,SKUNum,ProductNumber FROM ( SELECT wareHouse_id WareHouseID,MoveType,COUNT(DISTINCT task_no) TaskNum,COUNT(DISTINCT CONCAT(picking_batch_task_no,SKU)) SKUNum FROM (SELECT picking_batch_no FROM t_stk_moving_picking_batch WHERE DATE(create_time)=CURRENT_DATE AND customer_id=1) a JOIN (SELECT picking_batch_no,product_number,picking_batch_task_no FROM t_stk_moving_picking_task WHERE customer_id=1) b ON a.picking_batch_no=b.picking_batch_no JOIN (SELECT id,picking_batch_no,CASE task_type WHEN 1 THEN '存储移订单' WHEN 2 THEN '存储移订单' WHEN 3 THEN '订单移存储' WHEN 4 THEN '订单移订单' END MoveType,wareHouse_id FROM t_stk_moving_task WHERE customer_id=1 AND task_type IN (1,2,3,4)) c ON a.picking_batch_no=c.picking_batch_no JOIN (SELECT moving_task_id,sku_number,sku,task_no FROM t_stk_moving_task_detail WHERE customer_id=1) d ON d.moving_task_id=c.id GROUP BY wareHouse_id,MoveType ) A JOIN ( SELECT wareHouse_id WareHouseID,MoveType,SUM(product_number) ProductNumber FROM (SELECT picking_batch_no FROM t_stk_moving_picking_batch WHERE DATE(create_time)=CURRENT_DATE AND customer_id=1) a JOIN (SELECT picking_batch_no,product_number,picking_batch_task_no FROM t_stk_moving_picking_task WHERE customer_id=1) b ON a.picking_batch_no=b.picking_batch_no JOIN (SELECT DISTINCT picking_batch_no,CASE task_type WHEN 1 THEN '存储移订单' WHEN 2 THEN '存储移订单' WHEN 3 THEN '订单移存储' WHEN 4 THEN '订单移订单' END MoveType,wareHouse_id FROM t_stk_moving_task WHERE customer_id=1 AND task_type IN (1,2,3,4)) c ON a.picking_batch_no=c.picking_batch_no GROUP BY wareHouse_id,MoveType ) B ON A.WareHouseID=B.WareHouseID AND A.MoveType=B.MoveType) C ON main.MoveType=C.MoveType AND main.WareHouseID=C.WareHouseID LEFT JOIN ( -- 移库打印 （待完成） SELECT warehouse_id WarehouseID,MoveType, COUNT(DISTINCT b.task_no) as TaskNum, COUNT(DISTINCT c.sku) SKUNum, SUM(c.sku_number) ProductNumber FROM (SELECT id,warehouse_id,task_no,CASE task_type WHEN 1 THEN '存储移订单' WHEN 2 THEN '存储移订单' WHEN 3 THEN '订单移存储' WHEN 4 THEN '订单移订单' END MoveType FROM t_stk_moving_task WHERE task_status=0 AND task_type IN (1,2,3,4) AND customer_id=1 AND DATE(task_create_time) BETWEEN DATE_SUB(CURRENT_DATE,INTERVAL 14 DAY) AND CURRENT_DATE) b join (SELECT moving_task_id,sku,sku_number FROM t_stk_moving_task_detail WHERE customer_id=1 ) c on b.id=c.moving_task_id GROUP BY warehouse_id,MoveType ) D ON main.MoveType=D.MoveType AND main.WareHouseID=D.WareHouseID LEFT JOIN ( -- 移库拣货（已完成） SELECT a.WareHouseID,a.MoveType,TaskNum,SKUNum,ProductNumber FROM (SELECT wareHouse_id WareHouseID,MoveType,COUNT(DISTINCT task_no) TaskNum,COUNT(DISTINCT CONCAT(b.picking_batch_task_no,d.SKU)) SKUNum FROM (SELECT picking_batch_no,product_number,picking_batch_task_no FROM t_stk_moving_picking_task WHERE picking_status=2 AND DATE(picking_time)=CURRENT_DATE AND customer_id=1 ) b JOIN (SELECT id,origin_order_code,picking_batch_no,CASE task_type WHEN 1 THEN '存储移订单' WHEN 2 THEN '存储移订单' WHEN 3 THEN '订单移存储' WHEN 4 THEN '订单移订单' END MoveType,wareHouse_id FROM t_stk_moving_task WHERE customer_id=1 AND task_type IN (1,2,3,4)) c ON b.picking_batch_no=c.picking_batch_no JOIN (SELECT moving_task_id,sku_number,sku,order_code,task_no FROM t_stk_moving_task_detail W...;
##袁慧斌,人工修改过##
AISQL修改建议：碎片化SQL 补全 缺失的索引
explain表：
额外建议：
索引建议：
重现步骤