我正在嘗試從名為 orders 的表中找到物流公司和貨運公司的綜合表現。我寫了一個作業代碼，但它作業得很慢，我希望它作業得更快。
作業代碼是；
'SELECT
a.*,
b.number_of_order_lost_or_damaged,
c.number_of_order_lost_tracking FROM
(SELECT
logistic_partner,
cargo_partner,
round(AVG(DATEDIFF(date_delivered, date_add)),2) as time_spent_in_delivery
FROM
orders
WHERE
status IN (6)
GROUP BY logistic_partner , cargo_partner) a
JOIN
(SELECT
logistic_partner,
cargo_partner,
COUNT(id) AS number_of_order_lost_or_damaged
FROM
orders
WHERE
status IN (8)
GROUP BY logistic_partner , cargo_partner) b USING (logistic_partner , cargo_partner)
JOIN
(SELECT
logistic_partner,
cargo_partner,
COUNT(id) AS number_of_order_lost_tracking
FROM
orders
WHERE
status IN (10)
GROUP BY logistic_partner , cargo_partner) c USING (logistic_partner , cargo_partner);'
不作業的代碼是
'SELECT
o.logistic_partner,
o.cargo_partner,
round(AVG(DATEDIFF(a.date_delivered, a.date_add)),2) as time_spent_in_delivery,
count(b.id),
count(c.id) FROM orders o
JOIN orders a on a.id=o.id and a.status = 6
JOIN orders b on o.id=b.id and b.status = 8
JOIn orders c on c.id=o.id and c.status = 10
GROUP BY logistic_partner , cargo_partner;'
但這些是分開作業的：
'SELECT
o.logistic_partner,
o.cargo_partner,
round(AVG(DATEDIFF(a.date_delivered, a.date_add)),2) as time_spent_in_delivery FROM orders o
JOIN orders a on a.id=o.id and a.status = 6
GROUP BY logistic_partner , cargo_partner;'
要么
SELECT
o.logistic_partner,
o.cargo_partner,
count(b.id) FROM orders o
JOIN orders b on o.id=b.id and b.status = 8
GROUP BY logistic_partner , cargo_partner;
可能是什么問題呢 ？
uj5u.com熱心網友回復：
我的猜測是您不需要任何這些派生表。使用條件聚合應該產生相同的結果。例如：
SELECT logistic_partner
, cargo_partner
, ROUND(AVG(IF(status = 6, DATEDIFF(date_delivered, date_add), NULL)),2) AS time_spent_in_delivery
, SUM( IF( status = 8, 1, 0) ) AS number_of_order_lost_or_damaged
, SUM( IF( status = 10, 1, 0) ) AS number_of_order_lost_tracking
FROM orders
GROUP BY logistic_partner
, cargo_partner
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/449607.html
上一篇：MYSQLTRIGGER(BEFOREINSERT)給我NULL
下一篇：連接兩個表并對兩者中的列求和