SELECT IFNULL(SUM(p_lp_amt)+SUM(f_amt)-SUM(IFNULL(p_change_amt,0))-IFNULL(SUM((SELECT
SUM(B.p_promo_amt+B.f_promo_amt)
FROM t_trade_order_item_promotion B WHERE B.order_id=A.id AND promotion_category='rule'
AND cost_center='shop')),0),0) saleAmount,
SUM(IFNULL(paid_amt,0)) onlinePayAmt,
SUM(IFNULL(paid_amt,0)+IFNULL(p_cashpay_amt+f_cashpay_amt,0)) onlineAndCashPayAmt,
SUM(CASE
WHEN channel_id IN (8,32) THEN 1
ELSE 0
END) memberOrderCount,
COUNT(1) orderCount
FROM t_trade_order_header A
LEFT JOIN t_trade_order_delivery B
ON A.id = B.order_id
WHERE order_status = 'os.in.progress' and order_status='os.shipping' and order_status='os.completed'
AND channel_id IN (1,2,4,8,16,32,64)
AND A.shop_id = "204262"
你好,能否用 show create table 提供下这3个表的 schema?
另外,我发现 SQl 语句中显示的是 WHERE order_status = 'os.in.progress' and order_status='os.shipping' and order_status='os.completed' 而 TiDB 查询中显示的是 WHERE order_status IN ..., 这里能解释下吗?
explain analyze SELECT IFNULL(SUM(p_lp_amt)+SUM(f_amt)-SUM(IFNULL(p_change_amt,0))-IFNULL(SUM((SELECT
SUM(B.p_promo_amt+B.f_promo_amt)
FROM t_trade_order_item_promotion B WHERE B.order_id=A.id AND promotion_category='rule'
AND cost_center='shop')),0),0) saleAmount,
SUM(IFNULL(paid_amt,0)) onlinePayAmt,
SUM(IFNULL(paid_amt,0)+IFNULL(p_cashpay_amt+f_cashpay_amt,0)) onlineAndCashPayAmt,
SUM(CASE
WHEN channel_id IN (8,32) THEN 1
ELSE 0
END) memberOrderCount,
COUNT(1) orderCount
FROM t_trade_order_header A
LEFT JOIN t_trade_order_delivery B
ON A.id = B.order_id
WHERE order_status = 'os.in.progress' and order_status='os.shipping' and order_status='os.completed'
AND channel_id IN (1,2,4,8,16,32,64)
AND A.shop_id = "204262";
和 mysql 中
explain format=json SELECT IFNULL(SUM(p_lp_amt)+SUM(f_amt)-SUM(IFNULL(p_change_amt,0))-IFNULL(SUM((SELECT
SUM(B.p_promo_amt+B.f_promo_amt)
FROM t_trade_order_item_promotion B WHERE B.order_id=A.id AND promotion_category='rule'
AND cost_center='shop')),0),0) saleAmount,
SUM(IFNULL(paid_amt,0)) onlinePayAmt,
SUM(IFNULL(paid_amt,0)+IFNULL(p_cashpay_amt+f_cashpay_amt,0)) onlineAndCashPayAmt,
SUM(CASE
WHEN channel_id IN (8,32) THEN 1
ELSE 0
END) memberOrderCount,
COUNT(1) orderCount
FROM t_trade_order_header A
LEFT JOIN t_trade_order_delivery B
ON A.id = B.order_id
WHERE order_status = 'os.in.progress' and order_status='os.shipping' and order_status='os.completed'
AND channel_id IN (1,2,4,8,16,32,64)
AND A.shop_id = "204262";