mysql - mysql 中联接的替代方案

Question

我有一个如下查询，它检索表中不同商店的状态并将其显示为不同的列。

SELECT a.Store_ID,b.total as order_completed,c.total as order_cancelled,d.total as order_processed,e.total as order_failed FROM ORDER_HISTORY a
    -> LEFT OUTER JOIN(select Store_ID,count(*) as total from ORDER_HISTORY where Status = 57 group by Store_ID)b on a.Store_ID = b.Store_ID
    -> LEFT OUTER JOIN(select Store_ID,count(*) as total from ORDER_HISTORY where Status = 53 group by Store_ID)c on a.Store_ID = c.Store_ID
    -> LEFT OUTER JOIN(select Store_ID,count(*) as total from ORDER_HISTORY where Status = 52 group by Store_ID)d on a.Store_ID = d.Store_ID
    -> LEFT OUTER JOIN(select Store_ID,count(*) as total from ORDER_HISTORY where Status = 62 group by Store_ID)e on a.Store_ID = e.Store_ID
    -> group by a.Store_ID;

任何人都可以建议使用联接的替代方法，因为它会影响数据库操作的性能。

Answer 1

通过(Store_ID, Status)在ORDER_HISTORY上创建索引，那么这应该足够快。

SELECT
    Store_ID,
    status,
    COUNT(*) as total
FROM
    ORDER_HISTORY
GROUP BY
    Store_ID,
    status;

然后使用您的应用程序在列中显示少数结果行数据。实现起来应该不难。

<小时/>

另一种方法是(与上面相同的索引):

SELECT
    Store_ID,
    SUM(CASE WHEN Status = 57 THEN 1 ELSE 0 END) AS order_completed,
    SUM(CASE WHEN Status = 53 THEN 1 ELSE 0 END) AS order_cancelled,
    SUM(CASE WHEN Status = 52 THEN 1 ELSE 0 END) AS order_processed,
    SUM(CASE WHEN Status = 62 THEN 1 ELSE 0 END) AS order_processed
FROM
    ORDER_HISTORY
GROUP BY
    Store_ID;

根据需要替换 NULL 值。