zhoubin7 commented on issue #24058: URL: https://github.com/apache/shardingsphere/issues/24058#issuecomment-1423675797
> 请启用`sql-show`（[参考道具](https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/rules/sharding/)）并在日志中提供逻辑 SQL 和实际 SQL。 -- Logic SQL: SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_perform ance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1) -- Actual SQL: ds_jnmt ::: SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_perform ance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1) -- Actual SQL: ds_jnmt_ymlj ::: SELECT IFNULL(sum(o.order_amount - o.refund_amount), 0.00) AS todayRealTimeTurnover, IFNULL(SUM(CASE WHEN ori.is_first_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFirstOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_follow_order = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFollowOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_fission = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayFissionOrderTurnover, IFNULL(SUM(CASE WHEN ori.is_repurchase = 1 THEN o.order_amount - o.refund_amount END), 0.00) AS todayRepurchaseOrderTurnover FROM t_orders AS o INNER JOIN t_order_rel_info AS ori ON ori.orders_id = o.id AND o.tenant_id = -1 AND ori.tenant_id = -1 WHERE o.create_time >= date_format(now(), '%Y-%m-%d 00:00:00') AND o.create_time <= date_format(now(), '%Y-%m-%d 23:59:59') AND o.delete_state = 0 AND o.order_state NOT IN (6, 8, 10, 11) AND ori.no_performance_type IN (SELECT dict_value FROM sys_dict_data WHERE dict_type = 'statistics_no_perform ance_type' AND sys_dict_data.tenant_id = -1) AND NOT EXISTS (SELECT 1 FROM t_order_hang_up WHERE order_id = o.id AND t_order_hang_up.tenant_id = -1) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
