mysql中连接的替代方案

smdncfj3 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(289)

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

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;

有人能推荐一种替代使用连接的方法吗，因为它会影响数据库操作的性能。

mysql Join Database

来源：https://stackoverflow.com/questions/50169662/alternatives-to-joins-in-mysql

2条答案

按热度按时间

vulvrdjw1#

在上创建索引 ORDER_HISTORY 结束 (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;

根据需要替换空值。

赞(0）回复(0）举报 2021-06-21

fwzugrvs2#

尝试使用触发器。与数据库中发生事件时执行的存储过程相同。也许它会帮助你。

赞(0）回复(0）举报 2021-06-21