重现步骤
反馈人：袁慧斌
数据库：zhcxkj-center-warehouse
查询时间：10.011
扫描行数：412222
发送行数：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修改建议：
explain表：
额外建议：检查 子查询 ，大量表分类
索引建议：

历史记录  
  1. 2025-08-06 08:52:12, 由 陈莉珍 创建。
  2. 2025-08-06 10:18:51, 由 程伟 指派给 唐飞飞。
    修改了 指派给，旧值为 "chengwei"，新值为 "tangfeifei"。
  3. 2025-08-06 14:20:40, 由 唐飞飞 指派给 李健兴。
    修改了 指派给，旧值为 "tangfeifei"，新值为 "lijianxing"。
    该功能是统计移库任务列表界面待生成移库任务产品数，开发的时候就已经考虑到了这个sql会比较慢，所以做成了异步加载，而且只有每次进入列表界面的时候才会触发查询。如果需要优化的话，可以提需求处理
  4. 2025-08-11 14:06:18, 由 李健兴 确认Bug。
    修改了 是否确认，旧值为 "0"，新值为 "1"。
    此功能暂时不做修改
  5. 2025-08-11 14:08:02, 由 李健兴 指派给 陈莉珍。
    修改了 指派给，旧值为 "lijianxing"，新值为 "chenlizhen"。
  6. 2025-08-11 19:23:30, 由 陈莉珍 解决，方案为 第三方原因（外部系统） 。
    修改了 解决方案，旧值为 ""，新值为 "external"。
    修改了 解决版本，旧值为 ""，新值为 "pro627"。
    修改了 解决日期，旧值为 ""，新值为 "2025-08-11 19:23:24"。
    修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
    修改了 解决者 ，旧值为 ""，新值为 "chenlizhen"。
  7. 2025-08-11 19:24:17, 由 陈莉珍 编辑。
    修改了 所属模块 ，旧值为 "0"，新值为 "1888"。
    修改了 业务部门 ，旧值为 "0"，新值为 "2"。
    修改了 开发责任人，旧值为 ""，新值为 "lijianxing"。
    修改了 测试责任人，旧值为 ""，新值为 "lijianxing"。
    修改了 解决时效 ，旧值为 "0"，新值为 "82.52"。
    修改了 重现步骤 ，区别为：
    001- 反馈人：袁慧斌
    001+ 反馈人：袁慧斌 数据库：zhcxkj-center-warehouse 查询时间：10.011 扫描行数：412222 发送行数：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 &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 , ? ) = ? 真实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; 袁慧斌,人工修改过 AISQL修改建议： explain表： 额外建议：检查 子查询 ，大量表分类 索引建议：
    002- 数据库：zhcxkj-center-warehouse
    003- 查询时间：10.011
    004- 扫描行数：412222
    005- 发送行数：1
    006- 指纹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 , ? ) = ?
    007- 真实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;
    008- 袁慧斌,人工修改过
    009- AISQL修改建议：
    010- explain表：
    011- 额外建议：检查 子查询 ，大量表分类
    012- 索引建议：
    013-
    001- 反馈人：袁慧斌<br />
    001+ 反馈人：袁慧斌<br /> 数据库：zhcxkj-center-warehouse<br /> 查询时间：10.011<br /> 扫描行数：412222<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修改建议：<br /> explain表：<br /> 额外建议：检查 子查询 ，大量表分类 <br /> 索引建议：<br /><br />
    002- 数据库：zhcxkj-center-warehouse<br />
    003- 查询时间：10.011<br />
    004- 扫描行数：412222<br />
    005- 发送行数：1<br />
    006- 指纹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 />
    007- 真实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 />
    008- 袁慧斌,人工修改过<br />
    009- AISQL修改建议：<br />
    010- explain表：<br />
    011- 额外建议：检查 子查询 ，大量表分类 <br />
    012- 索引建议：<br />
    013- <br />
  8. 2025-08-11 19:24:20, 由 陈莉珍 关闭。
    修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
    修改了 指派给 ，旧值为 "chenlizhen"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "chenlizhen"。
    修改了 关闭日期，旧值为 ""，新值为 "2025-08-11 19:24:20"。
所属项目 技术中心-需求池
所属模块 供应链   仓储物流   备库订单
所属计划
所属迭代
Bug类型
严重程度 一般
优先级 P2
Bug状态 已关闭
业务部门 总经办
影响功能
开发责任人 李健兴
测试责任人 李健兴
解决时效 82.52
激活次数 0
激活日期
是否确认 是
当前指派 Closed 于 2025-08-11 19:24:20
截止日期 0000-00-00
上线时间 2025-08-06 14:20:00
上线时效 5.46
操作系统
浏览器
关键词
抄送给
所属迭代
相关需求
相关任务
由谁创建 陈莉珍 于 2025-08-06 08:52:12
影响环境 生产环境-业务反馈
由谁解决 陈莉珍 于 2025-08-11 19:23:24
解决版本 生产环境-业务反馈
解决方案 第三方原因（外部系统）
由谁关闭 陈莉珍 于 2025-08-11 19:24:20
最后修改 陈莉珍 于 2025-08-11 19:24:20
相关Bug