MySQL中JOIN的替代方案：门店多订单状态列转行查询优化咨询

Hey there! Instead of chaining multiple LEFT OUTER JOIN clauses to pivot your order status metrics into separate columns, you can use conditional aggregation in MySQL. This approach is way more concise, easier to maintain, and usually faster since it only scans the ORDER_HISTORY table once (compared to scanning it multiple times with each JOIN).

Here's how you can rewrite your query:

SELECT
  Store_ID,
  COUNT(CASE WHEN Status = 57 THEN 1 END) AS order_completed,
  COUNT(CASE WHEN Status = 53 THEN 1 END) AS order_cancelled,
  COUNT(CASE WHEN Status = [your_processed_status_code] THEN 1 END) AS order_processed,
  COUNT(CASE WHEN Status = [your_failed_status_code] THEN 1 END) AS order_failed
FROM ORDER_HISTORY
GROUP BY Store_ID;

How this works: #

• The CASE WHEN statement checks if the row matches a specific status code. If it does, it returns 1; otherwise, it returns NULL.
• COUNT() ignores NULL values, so it only counts rows where the status matches the condition.
• We group by Store_ID to get the aggregated counts per store.

Bonus tips: #

• If you prefer using SUM() instead of COUNT(), you can write SUM(CASE WHEN Status = 57 THEN 1 ELSE 0 END)—it will give you the exact same result.
• Adding new status columns is as simple as adding another COUNT(CASE...) line, no need to create new subqueries and JOINs.
• This approach avoids potential issues with duplicate rows that can sometimes happen when joining aggregated subqueries (though that's not a problem here with COUNT(*) in subqueries, but it's still a nice benefit).

内容的提问来源于stack exchange，提问作者MeenuAbhi