我有这个问题
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的数量增加了那么多,我需要添加联合
有人帮我优化这个查询到更好的方式吗
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'
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
wmtdaxz32#
考虑以下几点。我漏掉了总数,因为不太清楚该加什么。