深圳智汇创想科技有限公司

001- 反馈人：王凯
001+ 反馈人：王凯 数据库：zhcxkj-center-warehouse 查询时间：13.2217 执行次数: 181 扫描行数：447781 发送行数：1 指纹SQL: SELECT SUM ( b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) ) sumProductNumber 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 < ? then b . sku_number else ? end ) as sumNumber FROM ? 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 ( ? , ? ) 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 , ? ) ) sumShelfNumber FROM ? 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 = ? 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 ? w WHERE IFNULL ( w . waiting_move_state , ? ) = ? GROUP BY w . order_code , w . customer_id ) k ON k . order_code = a . order_code AND a . customer_id = k . customer_id WHERE a . add_time >= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . order_state IN ( ? , ? ) AND a . order_status = ? AND a . is_stock_order = ? AND b . purchase_type < ? AND b . lock_num > ? AND IFNULL ( f . sumNumber , ? ) >= ? AND IFNULL ( k . waitMovingOrderCount , ? ) = ? 真实SQL：SELECT SUM(b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0)) sumProductNumber 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 < 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 WHERE a.add_time >= 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 < 2 AND b.lock_num > 0 AND IFNULL(f.sumNumber,0)>=0 AND IFNULL(k.waitMovingOrderCount,0)=0 AND IFNULL(b.pending_move_number,0)>=IFNULL(g.sumShelfNumber,0) AND (b.product_num + IFNULL(b.product_num_update,0))>0 AND b.lock_num -IFNULL(b.pending_move_number,0)-IFNULL(f.sumNumber,0)-b.put_in_num+IFNULL(g.sumShelfNumber,0) > 0; 王凯,人工修改过 AISQL修改建议：SQL勿多层嵌套和过多连表，拆分业务，使用代码拼接组装数据 explain表： 额外建议： 索引建议：
002- 数据库：zhcxkj-center-warehouse
003- 查询时间：13.2217
004- 执行次数: 181
005- 扫描行数：447781
006- 发送行数：1
007- 指纹SQL: SELECT SUM ( b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) ) sumProductNumber 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 < ? then b . sku_number else ? end ) as sumNumber FROM ? 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 ( ? , ? ) 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 , ? ) ) sumShelfNumber FROM ? 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 = ? 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 ? w WHERE IFNULL ( w . waiting_move_state , ? ) = ? GROUP BY w . order_code , w . customer_id ) k ON k . order_code = a . order_code AND a . customer_id = k . customer_id WHERE a . add_time >= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . order_state IN ( ? , ? ) AND a . order_status = ? AND a . is_stock_order = ? AND b . purchase_type < ? AND b . lock_num > ? AND IFNULL ( f . sumNumber , ? ) >= ? AND IFNULL ( k . waitMovingOrderCount , ? ) = ?
008- 真实SQL：SELECT SUM(b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0)) sumProductNumber 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 < 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 WHERE a.add_time >= 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 < 2 AND b.lock_num > 0 AND IFNULL(f.sumNumber,0)>=0 AND IFNULL(k.waitMovingOrderCount,0)=0 AND IFNULL(b.pending_move_number,0)>=IFNULL(g.sumShelfNumber,0) AND (b.product_num + IFNULL(b.product_num_update,0))>0 AND b.lock_num -IFNULL(b.pending_move_number,0)-IFNULL(f.sumNumber,0)-b.put_in_num+IFNULL(g.sumShelfNumber,0) > 0;
009- 王凯,人工修改过
010- AISQL修改建议：SQL勿多层嵌套和过多连表，拆分业务，使用代码拼接组装数据
011- explain表：
012- 额外建议：
013- 索引建议：
014-

001- 反馈人：王凯<br />
001+ 反馈人：王凯<br /> 数据库：zhcxkj-center-warehouse<br /> 查询时间：13.2217<br /> 执行次数: 181<br /> 扫描行数：447781<br /> 发送行数：1<br /> 指纹SQL: SELECT SUM ( b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) ) sumProductNumber 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 &lt; ? then b . sku_number else ? end ) as sumNumber FROM ? 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 ( ? , ? ) 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 , ? ) ) sumShelfNumber FROM ? 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 = ? 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 ? w WHERE IFNULL ( w . waiting_move_state , ? ) = ? GROUP BY w . order_code , w . customer_id ) k ON k . order_code = a . order_code AND a . customer_id = k . customer_id WHERE a . add_time &gt;= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . order_state IN ( ? , ? ) AND a . order_status = ? AND a . is_stock_order = ? AND b . purchase_type &lt; ? AND b . lock_num &gt; ? AND IFNULL ( f . sumNumber , ? ) &gt;= ? AND IFNULL ( k . waitMovingOrderCount , ? ) = ? <br /> 真实SQL：SELECT SUM(b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0)) sumProductNumber 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 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 IFNULL(f.sumNumber,0)&gt;=0 AND IFNULL(k.waitMovingOrderCount,0)=0 AND IFNULL(b.pending_move_number,0)&gt;=IFNULL(g.sumShelfNumber,0) AND (b.product_num + IFNULL(b.product_num_update,0))&gt;0 AND b.lock_num -IFNULL(b.pending_move_number,0)-IFNULL(f.sumNumber,0)-b.put_in_num+IFNULL(g.sumShelfNumber,0) &gt; 0;<br /> 王凯,人工修改过<br /> AISQL修改建议：SQL勿多层嵌套和过多连表，拆分业务，使用代码拼接组装数据<br /> explain表：<br /> 额外建议：<br /> 索引建议：<br /><br />
002- 数据库：zhcxkj-center-warehouse<br />
003- 查询时间：13.2217<br />
004- 执行次数: 181<br />
005- 扫描行数：447781<br />
006- 发送行数：1<br />
007- 指纹SQL: SELECT SUM ( b . lock_num - IFNULL ( b . pending_move_number , ? ) - b . put_in_num - IFNULL ( f . sumNumber , ? ) + IFNULL ( g . sumShelfNumber , ? ) ) sumProductNumber 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 &lt; ? then b . sku_number else ? end ) as sumNumber FROM ? 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 ( ? , ? ) 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 , ? ) ) sumShelfNumber FROM ? 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 = ? 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 ? w WHERE IFNULL ( w . waiting_move_state , ? ) = ? GROUP BY w . order_code , w . customer_id ) k ON k . order_code = a . order_code AND a . customer_id = k . customer_id WHERE a . add_time &gt;= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . order_state IN ( ? , ? ) AND a . order_status = ? AND a . is_stock_order = ? AND b . purchase_type &lt; ? AND b . lock_num &gt; ? AND IFNULL ( f . sumNumber , ? ) &gt;= ? AND IFNULL ( k . waitMovingOrderCount , ? ) = ? <br />
008- 真实SQL：SELECT SUM(b.lock_num -IFNULL(b.pending_move_number,0)-b.put_in_num-IFNULL(f.sumNumber,0)+IFNULL(g.sumShelfNumber,0)) sumProductNumber 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 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 IFNULL(f.sumNumber,0)&gt;=0 AND IFNULL(k.waitMovingOrderCount,0)=0 AND IFNULL(b.pending_move_number,0)&gt;=IFNULL(g.sumShelfNumber,0) AND (b.product_num + IFNULL(b.product_num_update,0))&gt;0 AND b.lock_num -IFNULL(b.pending_move_number,0)-IFNULL(f.sumNumber,0)-b.put_in_num+IFNULL(g.sumShelfNumber,0) &gt; 0;<br />
009- 王凯,人工修改过<br />
010- AISQL修改建议：SQL勿多层嵌套和过多连表，拆分业务，使用代码拼接组装数据<br />
011- explain表：<br />
012- 额外建议：<br />
013- 索引建议：<br />
014- <br />