在mysql中优化联合查询

bvhaajcl 于 2021-06-18 发布在 Mysql

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

我有这个问题

SELECT 
    ID, isDebit, AccountID, SUM(Amount) AS Amount
FROM
    journaltransactions
WHERE
    AccountID <> 78 and date between '2018-11-29' and '2018-11-29'
    union
    SELECT 
    ID, isDebit, AccountID, SUM(Amount) AS Amount
FROM
    journaltransactions
WHERE
    AccountID <> 795 and date between '2018-11-29' and '2018-11-29'
    union
    SELECT 
    ID, isDebit, AccountID, SUM(Amount) AS Amount
FROM
    journaltransactions
WHERE
    AccountID <> 56 and date between '2018-11-29' and '2018-11-29'
    union
    SELECT 
    ID, isDebit, AccountID, SUM(Amount) AS Amount
FROM
    journaltransactions
WHERE
    AccountID <> 89 and date between '2018-11-29' and '2018-11-29'
    union
    SELECT 
    ID, isDebit, AccountID, SUM(Amount) AS Amount
FROM
    journaltransactions
WHERE
    AccountID <> 90 and date between '2018-11-29' and '2018-11-29'

这里78795,56,89,90是条件的帐户ID集，如果ID的数量增加了那么多，我需要添加联合
有人帮我优化这个查询到更好的方式吗

mysql union query-optimization

来源：https://stackoverflow.com/questions/53607460/optimize-an-union-query-in-mysql

2条答案

按热度按时间

mmvthczy1#

你可以用 NOT IN ```
SELECT
ID, isDebit, AccountID, SUM(Amount) AS Amount
FROM
journaltransactions
WHERE
AccountID NOT IN (78,795,56,89,90) and date between '2018-11-29' and '2018-11-29'

注意：既然你已经用过了 `SUM` 在sql中，需要使用 `GROUP BY` 为了得到想要的结果
对于您的问题，请尝试下面的，在这种情况下，我们从另一个相同的选项卡获取id，然后使用 `<>` 为了得到结果

SELECT
a.ID, a.isDebit, a.AccountID, SUM(a.Amount) AS Amount
FROM
journaltransactions a
JOIN journaltransactions b ON b.id IN (78,795,56,89,90);
WHERE
BETWEEN '2018-11-29' AND '2018-11-29'
AND a.id<>b.id
GROUP BY a.id

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

wmtdaxz32#

考虑以下几点。我漏掉了总数，因为不太清楚该加什么。

SELECT 
    ID, isDebit, AccountID
FROM
    journaltransactions
WHERE
   AccountID NOT IN (78,795,56,89,90) 
   and date = '2018-11-29'

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

我来回答

在mysql中优化联合查询

2条答案

相关问题

热门标签

最新问答