select count(0) from( SELECT oi.cust_id, ci.cust_name, ci.cust_num, ci.mobile, ci.org_full_name, ci.psn_type, ci.psn_type_name, oi.order_type, oi.if_online, oi.source_type, oi.machine_sn, oi.order_id, oi.order_date, oi.order_time, oi.pay_time, oi.canteen_id, oi.canteen_name, oi.stall_id, oi.stall_name, oi.room_id, oi.room_name, oi.mealtime_type, oi.mealtime_name, oi.payable_amount, oi.discounts_amount, oi.real_amount, oi.refund_amount, oi.delivery_amount, oi.packing_amount, oi.pay_type, oi.deduction_type, oi.delivery_type, oi.order_state, oi.pay_state, oi.order_refund_state, oi.dishes_state, oi.meal_code, oi.remark, oi.mac_order_id, oi.coupon_amount, oi.machine_num, oi.pay_channel, oi.nu_clear_mode, oi.operator_id, oi.operator_name, oi.cashier_id, oi.cashier_name, aa.area_id, aa.area_name, mc.coupon_name, ( CASE WHEN op.STATUS = 1 THEN op.order_photo_url ELSE '' END ) AS order_photo_url FROM order_info oi LEFT JOIN cust_info ci ON oi.cust_id = ci.cust_id LEFT JOIN mkt_coupon mc ON oi.coupon_id = mc.coupon_num LEFT JOIN order_photo op ON oi.mac_order_id = op.mac_order_id LEFT JOIN alloc_canteen ac ON oi.canteen_id = ac.canteen_id LEFT JOIN alloc_area aa ON ac.area_id = aa.area_id WHERE oi.order_date BETWEEN '2023-09-23' AND '2023-12-21' AND oi.mealtime_type IN ( 1, 2, 3, 4, 5 ) AND oi.canteen_id IN ( 193589143895281664, 259134882771374080, 259135372255039488, 259135512902635520, 259135648831639552 ) AND oi.stall_id IN ( 193589267795021824, 259134937536401408, 259135707497369600, 259135759884226560, 259135850808348672, - 1 ) AND oi.order_type IN ( 11, 21, 22 ) ) ================================================================================= |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| --------------------------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |1907219 | |1 |└─HASH RIGHT OUTER JOIN | |2015643 |1870687 | |2 | ├─TABLE FULL SCAN |op |1 |4 | |3 | └─HASH RIGHT OUTER JOIN | |2015643 |1340317 | |4 | ├─TABLE FULL SCAN |ci(index_cust_id) |102826 |3504 | |5 | └─HASH RIGHT OUTER JOIN | |2015643 |1088949 | |6 | ├─TABLE FULL SCAN |mc(index_coupon_num)|1 |4 | |7 | └─HASH RIGHT OUTER JOIN | |2015643 |831087 | |8 | ├─MERGE RIGHT OUTER JOIN | |5 |10 | |9 | │ ├─TABLE FULL SCAN |aa(index_area_id) |5 |4 | |10| │ └─SORT | |5 |5 | |11| │ └─TABLE FULL SCAN |ac |5 |5 | |12| └─TABLE FULL SCAN |oi |2015643 |600333 | ================================================================================= Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT(*)]) 1 - output(nil), filter(nil), rowset=256 equal_conds([oi.mac_order_id = op.mac_order_id]), other_conds(nil) 2 - output([op.mac_order_id]), filter(nil), rowset=256 access([op.mac_order_id]), partitions(p0) is_index_back=false, is_global_index=false, range_key([op.mac_order_id], [op.order_photo_type]), range(MIN,MIN ; MAX,MAX)always true 3 - output([oi.mac_order_id]), filter(nil), rowset=256 equal_conds([oi.cust_id = ci.cust_id]), other_conds(nil) 4 - output([ci.cust_id]), filter(nil), rowset=256 access([ci.cust_id]), partitions(p0) is_index_back=false, is_global_index=false, range_key([ci.cust_id], [ci.id]), range(MIN,MIN ; MAX,MAX)always true 5 - output([oi.cust_id], [oi.mac_order_id]), filter(nil), rowset=256 equal_conds([cast(oi.coupon_id, DECIMAL(-1, -1)) = cast(mc.coupon_num, DECIMAL(20, 0))]), other_conds(nil) 6 - output([mc.coupon_num]), filter(nil), rowset=256 access([mc.coupon_num]), partitions(p0) is_index_back=false, is_global_index=false, range_key([mc.coupon_num], [mc.id]), range(MIN,MIN ; MAX,MAX)always true 7 - output([oi.cust_id], [oi.coupon_id], [oi.mac_order_id]), filter(nil), rowset=256 equal_conds([oi.canteen_id = ac.canteen_id]), other_conds(nil) 8 - output([ac.canteen_id]), filter(nil), rowset=256 equal_conds([ac.area_id = aa.area_id]), other_conds(nil) merge_directions([ASC]) 9 - output([aa.area_id]), filter(nil), rowset=256 access([aa.area_id]), partitions(p0) is_index_back=false, is_global_index=false, range_key([aa.area_id], [aa.id]), range(MIN,MIN ; MAX,MAX)always true 10 - output([ac.area_id], [ac.canteen_id]), filter(nil), rowset=256 sort_keys([ac.area_id, ASC]) 11 - output([ac.canteen_id], [ac.area_id]), filter([ac.canteen_id IN (193589143895281664, 259134882771374080, 259135372255039488, 259135512902635520, 259135648831639552)]), rowset=256 access([ac.canteen_id], [ac.area_id]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([ac.id]), range(MIN ; MAX)always true 12 - output([oi.cust_id], [oi.coupon_id], [oi.mac_order_id], [oi.canteen_id]), filter([oi.order_type IN (11, 21, 22)], [oi.stall_id IN (193589267795021824, 259134937536401408, 259135707497369600, 259135759884226560, 259135850808348672, -1)], [cast(oi.order_date, DATETIME(-1, -1)) >= INTERNAL_FUNCTION('2023-09-23', 114, 17)], [cast(oi.order_date, DATETIME(-1, -1)) <= INTERNAL_FUNCTION('2023-12-21', 112, 17)], [oi.mealtime_type IN (1, 2, 3, 4, 5)], [oi.canteen_id IN (193589143895281664, 259134882771374080, 259135372255039488, 259135512902635520, 259135648831639552)]), rowset=256 access([oi.cust_id], [oi.coupon_id], [oi.mac_order_id], [oi.canteen_id], [oi.order_date], [oi.mealtime_type], [oi.stall_id], [oi.order_type]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false,false,false], range_key([oi.order_id]), range(MIN ; MAX)always true