时间戳

首页 > 解决方案 > 这个多重self-JOIN可以优化吗？

mysql - 这个多重self-JOIN可以优化吗？

问题描述

对不起，如果标题不够清楚。这是我的查询：

SELECT
    '>200000 €' as amount,
    Q1.asset as inbound,
    Q2.asset as outbound,
    Q3.asset as inside
FROM
    (
    SELECT COUNT(amount* base_rate) as asset
    FROM user_wallet_movement
    WHERE status = 'execute'
        AND direction = 'inbound'
        AND to_address is null
        AND mov_date > '2020-01-07'
        AND mov_date < '2021-06-30'
        AND amount* base_rate > 200000
        ) AS Q1
JOIN
        (
    SELECT COUNT(amount* base_rate) as asset
    FROM user_wallet_movement
    WHERE status = 'execute'
        AND direction = 'outbound'
        AND to_address is null
        AND mov_date > '2020-01-07'
        AND mov_date < '2021-06-30'
        AND amount* base_rate > 200000
        ) AS Q2
JOIN 
        (
    SELECT COUNT(amount* base_rate) as asset
    FROM user_wallet_movement
    WHERE status = 'execute'
        AND to_address is not null
        AND mov_date > '2020-01-07'
        AND mov_date < '2021-06-30'
        AND amount* base_rate > 200000
        ) AS Q3

如您所见，我加入了三个具有一些共同条件的查询。我的问题是：是否可以只写一次常见条件？

标签： mysqlsqlaggregate-functions

解决方案

我不清楚您的示例查询的意图，因此我的回答可能不会产生您正在寻找的结果。我很困惑为什么COUNT()不SUM()参与。

在任何情况下，您都可以应用条件聚合，因为所有值都来自同一个表。这里不需要 JOIN。

如果您尝试计算超过某些不同标准的所有交易，> 200000您可以执行以下操作：

SELECT
    '>200000 €' as amount,
    SUM(CASE WHEN direction = 'inbound' AND to_address is null 
        THEN 1 ELSE 0 END) AS inbound,
    SUM(CASE WHEN direction = 'outbound' AND to_address is null 
        THEN 1 ELSE 0 END) AS outbound,
    SUM(CASE WHEN to_address is not null 
        THEN 1 ELSE 0 END) AS inside
FROM user_wallet_movement
WHERE 
    (amount*base_rate) > 200000
    AND status = 'execute' 
    AND mov_date > '2020-01-07'
    AND mov_date < '2021-06-30'

如果您想要所有交易的总和以及入站/出站/内部的小计，您可以执行以下操作：

SELECT
    SUM(amount*base_rate) as total,
    SUM(CASE WHEN direction = 'inbound' AND to_address is null 
        THEN amount*base_rate ELSE 0 END) AS inbound,
    SUM(CASE WHEN direction = 'outbound' AND to_address is null 
        THEN amount*base_rate ELSE 0 END) AS outbound,
    SUM(CASE WHEN to_address is not null 
        THEN amount*base_rate ELSE 0 END) AS inside
FROM user_wallet_movement
WHERE 
    AND status = 'execute' 
    AND mov_date > '2020-01-07'
    AND mov_date < '2021-06-30'

推荐阅读