001-
001+ <a href="https://alidocs.dingtalk.com/i/nodes/7NkDwLng8ZMxRd0DuzMrKkOoJKMEvZBY?doc_type=wiki_doc">https://alidocs.dingtalk.com/i/nodes/7NkDwLng8ZMxRd0DuzMrKkOoJKMEvZBY?doc_type=wiki_doc 数据库：zhcxkj-center-warehouse 查询时间：10.9648 扫描行数：428376 发送行数：3803.57 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as taskType , b . lock_num as orderLockNum , CASE WHEN IFNULL ( e . transfer_type , ? ) = ? THEN IFNULL ( f . transfer_type , ? ) ELSE IFNULL ( e . transfer_type , ? ) end as transferType , d . order_source_country , ? as taskStatus , b . lock_num AS lock_num2 , IFNULL ( b . pending_move_number , ? ) pending_move_number , IFNULL ( b . put_in_num , ? ) put_in_num , IFNULL ( f . sumNumber , ? ) sumNumber , IFNULL ( g . sumShelfNumber , ? ) sumShelfNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . order_code , b . order_list_id , SUM ( case when a . task_status 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 1 as taskType,b.lock_num as orderLockNum, CASE WHEN IFNULL(e.transfer_type,-1)=-1 THEN IFNULL(f.transfer_type,-1) ELSE IFNULL(e.transfer_type,-1) end as transferType, d.order_source_country, 0 as taskStatus, b.lock_num AS lock_num2, IFNULL(b.pending_move_number,0) pending_move_number, IFNULL(b.put_in_num,0) put_in_num, IFNULL(f.sumNumber,0) sumNumber, IFNULL(g.sumShelfNumber,0) sumShelfNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.order_code,b.order_list_id, SUM(case when a.task_status &lt; 4 then b.sku_number else 0 end) as sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type in(1,2) GROUP BY a.customer_id,a.order_code,b.order_list_id )f on f.order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN ( SELECT a.customer_id,b.order_list_id,SUM(IFNULL(b.moved_number,0)) sumShelfNumber FROM t_stk_moving_task a join t_stk_moving_task_detail b on a.id=b.moving_task_id AND a.customer_id=b.customer_id where a.task_type=3 GROUP by a.customer_id,b.order_list_id )g on g.order_list_id=b.id AND g.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id Left join t_stk_transport f on a.transport_id=f.Id left join ( SELECT w.order_code,w.customer_id,COUNT(w.id) waitMovingOrderCount from rb_waitingmoving_orderdetail w WHERE IFNULL(w.waiting_move_state,0)=0 GROUP BY w.order_code,w.customer_id )k ON k.order_code=a.order_code AND a.customer_id=k.customer_id left join t_stk_stockcheck_sku tsssku on a.customer_id=tsssku.customer_id and a.warehouse_id=tsssku.warehouse_id and b.sku=tsssku.sku and tsssku.is_check=1 WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.order_state IN (0,1) AND a.order_status=0 AND a.is_stock_order = 1 AND b.purchase_type &lt; 2 AND b.lock_num &gt; 0 AND tsssku.id IS NULL ORDER BY a.add_time; 王凯,人工修改过 AISQL修改建议：勿使用多层嵌套和过多连表，请拆分业务 explain表： 额外建议： 索引建议：
002- 数据库：zhcxkj-center-warehouse
003- 查询时间：10.9648
004- 扫描行数：428376
005- 发送行数：3803.57
006- 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as taskType , b . lock_num as orderLockNum , CASE WHEN IFNULL ( e . transfer_type , ? ) = ? THEN IFNULL ( f . transfer_type , ? ) ELSE IFNULL ( e . transfer_type , ? ) end as transferType , d . order_source_country , ? as taskStatus , b . lock_num AS lock_num2 , IFNULL ( b . pending_move_number , ? ) pending_move_number , IFNULL ( b . put_in_num , ? ) put_in_num , IFNULL ( f . sumNumber , ? ) sumNumber , IFNULL ( g . sumShelfNumber , ? ) sumShelfNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . order_code , b . order_list_id , SUM ( case when a . task_status
007- 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 1 as taskType,b.lock_num as orderLockNum, CASE WHEN IFNULL(e.transfer_type,-1)=-1 THEN IFNULL(f.transfer_type,-1) ELSE IFNULL(e.transfer_type,-1) end as transferType, d.order_source_country, 0 as taskStatus, b.lock_num AS lock_num2, IFNULL(b.pending_move_number,0) pending_move_number, IFNULL(b.put_in_num,0) put_in_num, IFNULL(f.sumNumber,0) sumNumber, IFNULL(g.sumShelfNumber,0) sumShelfNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.order_code,b.order_list_id, SUM(case when a.task_status &lt; 4 then b.sku_number else 0 end) as sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type in(1,2) GROUP BY a.customer_id,a.order_code,b.order_list_id )f on f.order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN ( SELECT a.customer_id,b.order_list_id,SUM(IFNULL(b.moved_number,0)) sumShelfNumber FROM t_stk_moving_task a join t_stk_moving_task_detail b on a.id=b.moving_task_id AND a.customer_id=b.customer_id where a.task_type=3 GROUP by a.customer_id,b.order_list_id )g on g.order_list_id=b.id AND g.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id Left join t_stk_transport f on a.transport_id=f.Id left join ( SELECT w.order_code,w.customer_id,COUNT(w.id) waitMovingOrderCount from rb_waitingmoving_orderdetail w WHERE IFNULL(w.waiting_move_state,0)=0 GROUP BY w.order_code,w.customer_id )k ON k.order_code=a.order_code AND a.customer_id=k.customer_id left join t_stk_stockcheck_sku tsssku on a.customer_id=tsssku.customer_id and a.warehouse_id=tsssku.warehouse_id and b.sku=tsssku.sku and tsssku.is_check=1 WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.order_state IN (0,1) AND a.order_status=0 AND a.is_stock_order = 1 AND b.purchase_type &lt; 2 AND b.lock_num &gt; 0 AND tsssku.id IS NULL ORDER BY a.add_time;
008- 王凯,人工修改过
009- AISQL修改建议：勿使用多层嵌套和过多连表，请拆分业务
010- explain表：
011- 额外建议：
012- 索引建议：

001- <br />
001+ <a href="https://alidocs.dingtalk.com/i/nodes/7NkDwLng8ZMxRd0DuzMrKkOoJKMEvZBY?doc_type=wiki_doc">https://alidocs.dingtalk.com/i/nodes/7NkDwLng8ZMxRd0DuzMrKkOoJKMEvZBY?doc_type=wiki_doc</a><br /> 数据库：zhcxkj-center-warehouse<br /> 查询时间：10.9648<br /> 扫描行数：428376<br /> 发送行数：3803.57<br /> 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as taskType , b . lock_num as orderLockNum , CASE WHEN IFNULL ( e . transfer_type , ? ) = ? THEN IFNULL ( f . transfer_type , ? ) ELSE IFNULL ( e . transfer_type , ? ) end as transferType , d . order_source_country , ? as taskStatus , b . lock_num AS lock_num2 , IFNULL ( b . pending_move_number , ? ) pending_move_number , IFNULL ( b . put_in_num , ? ) put_in_num , IFNULL ( f . sumNumber , ? ) sumNumber , IFNULL ( g . sumShelfNumber , ? ) sumShelfNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . order_code , b . order_list_id , SUM ( case when a . task_status <br /> 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 1 as taskType,b.lock_num as orderLockNum, CASE WHEN IFNULL(e.transfer_type,-1)=-1 THEN IFNULL(f.transfer_type,-1) ELSE IFNULL(e.transfer_type,-1) end as transferType, d.order_source_country, 0 as taskStatus, b.lock_num AS lock_num2, IFNULL(b.pending_move_number,0) pending_move_number, IFNULL(b.put_in_num,0) put_in_num, IFNULL(f.sumNumber,0) sumNumber, IFNULL(g.sumShelfNumber,0) sumShelfNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.order_code,b.order_list_id, SUM(case when a.task_status &lt; 4 then b.sku_number else 0 end) as sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type in(1,2) GROUP BY a.customer_id,a.order_code,b.order_list_id )f on f.order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN ( SELECT a.customer_id,b.order_list_id,SUM(IFNULL(b.moved_number,0)) sumShelfNumber FROM t_stk_moving_task a join t_stk_moving_task_detail b on a.id=b.moving_task_id AND a.customer_id=b.customer_id where a.task_type=3 GROUP by a.customer_id,b.order_list_id )g on g.order_list_id=b.id AND g.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id Left join t_stk_transport f on a.transport_id=f.Id left join ( SELECT w.order_code,w.customer_id,COUNT(w.id) waitMovingOrderCount from rb_waitingmoving_orderdetail w WHERE IFNULL(w.waiting_move_state,0)=0 GROUP BY w.order_code,w.customer_id )k ON k.order_code=a.order_code AND a.customer_id=k.customer_id left join t_stk_stockcheck_sku tsssku on a.customer_id=tsssku.customer_id and a.warehouse_id=tsssku.warehouse_id and b.sku=tsssku.sku and tsssku.is_check=1 WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.order_state IN (0,1) AND a.order_status=0 AND a.is_stock_order = 1 AND b.purchase_type &lt; 2 AND b.lock_num &gt; 0 AND tsssku.id IS NULL ORDER BY a.add_time;<br /> 王凯,人工修改过<br /> AISQL修改建议：勿使用多层嵌套和过多连表，请拆分业务<br /> explain表：<br /> 额外建议：<br /> 索引建议：<br /><br />
002- 数据库：zhcxkj-center-warehouse<br />
003- 查询时间：10.9648<br />
004- 扫描行数：428376<br />
005- 发送行数：3803.57<br />
006- 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as taskType , b . lock_num as orderLockNum , CASE WHEN IFNULL ( e . transfer_type , ? ) = ? THEN IFNULL ( f . transfer_type , ? ) ELSE IFNULL ( e . transfer_type , ? ) end as transferType , d . order_source_country , ? as taskStatus , b . lock_num AS lock_num2 , IFNULL ( b . pending_move_number , ? ) pending_move_number , IFNULL ( b . put_in_num , ? ) put_in_num , IFNULL ( f . sumNumber , ? ) sumNumber , IFNULL ( g . sumShelfNumber , ? ) sumShelfNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . order_code , b . order_list_id , SUM ( case when a . task_status <br />
007- 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 1 as taskType,b.lock_num as orderLockNum, CASE WHEN IFNULL(e.transfer_type,-1)=-1 THEN IFNULL(f.transfer_type,-1) ELSE IFNULL(e.transfer_type,-1) end as transferType, d.order_source_country, 0 as taskStatus, b.lock_num AS lock_num2, IFNULL(b.pending_move_number,0) pending_move_number, IFNULL(b.put_in_num,0) put_in_num, IFNULL(f.sumNumber,0) sumNumber, IFNULL(g.sumShelfNumber,0) sumShelfNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.order_code,b.order_list_id, SUM(case when a.task_status &lt; 4 then b.sku_number else 0 end) as sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type in(1,2) GROUP BY a.customer_id,a.order_code,b.order_list_id )f on f.order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN ( SELECT a.customer_id,b.order_list_id,SUM(IFNULL(b.moved_number,0)) sumShelfNumber FROM t_stk_moving_task a join t_stk_moving_task_detail b on a.id=b.moving_task_id AND a.customer_id=b.customer_id where a.task_type=3 GROUP by a.customer_id,b.order_list_id )g on g.order_list_id=b.id AND g.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id Left join t_stk_transport f on a.transport_id=f.Id left join ( SELECT w.order_code,w.customer_id,COUNT(w.id) waitMovingOrderCount from rb_waitingmoving_orderdetail w WHERE IFNULL(w.waiting_move_state,0)=0 GROUP BY w.order_code,w.customer_id )k ON k.order_code=a.order_code AND a.customer_id=k.customer_id left join t_stk_stockcheck_sku tsssku on a.customer_id=tsssku.customer_id and a.warehouse_id=tsssku.warehouse_id and b.sku=tsssku.sku and tsssku.is_check=1 WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.order_state IN (0,1) AND a.order_status=0 AND a.is_stock_order = 1 AND b.purchase_type &lt; 2 AND b.lock_num &gt; 0 AND tsssku.id IS NULL ORDER BY a.add_time;<br />
008- 王凯,人工修改过<br />
009- AISQL修改建议：勿使用多层嵌套和过多连表，请拆分业务<br />
010- explain表：<br />
011- 额外建议：<br />
012- 索引建议：<br /><br />