时间戳

首页 > 解决方案 > mysql中join的替代方法

mysql - mysql中join的替代方法

问题描述

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

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;

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

标签： mysqldatabasejoin

解决方案

ORDER_HISTORY在over上创建一个索引(Store_ID, Status)，那么这应该很快。

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 值。

推荐阅读