SQL:根据两列值的组合进行筛选

(bank = 1 AND account = 2) OR (bank = 3 AND account = 4) OR ...

如果银行账户对列表是一个子查询,则编写如下内容:

SELECT * FROM balances b LEFT JOIN bank_account_pairs p ON ...
WHERE b.bank = p.bank AND b.account = p.account AND ...

如果您有驱动特定银行和账户实体列表的标准,那么您应该加入这些表。

你确实有银行表和账户表,是吗?

假设你在accounts表中有缩小要引用的特定帐户范围的信息,例如,假设你的accounts表有一个IsActive char(1)NOT NULL字段,你想要非活动帐户的余额,你可以写这样的内容:

SELECT date, sum( amount ) AS amount
FROM Balances b 
     INNER JOIN Accounts a 
     ON b.Bank = a.Bank AND b.Account = a.Account
WHERE a.IsActive = 'N'

从设计角度来看,您可能应该创建一个人工键来删除表中非标识数据的复制。这会给你这样的东西:

CREATE TABLE Accounts ( 
    AccountId int identity(1,1) NOT NULL,
    Bank nvarchar(15) NOT NULL,
    Account nvarchar(15) NOT NULL
)

CREATE TABLE Balances ( 
    AccountId int,
    Date datetime, 
    Amount money
)

SELECT date, sum( amount ) AS amount
FROM Balances b 
     INNER JOIN Accounts a 
     ON b.AccountId = a.AccountId
WHERE a.IsActive = 'N'

内部连接将强制执行银行+账户组合,但你必须小心,不要收到比预期更多的行,尤其是在计算时。

select b.date, sum(b.amount) as amount
from balances as b
where exists (select 1 from backaccounts as ba where ba.bank = b.bank and ba.account = b.account)
group by b.date

如果你不想做一个临时表,一个解决方案是这样的:

select bank, account, sum(amount)
from balances,
(select 'XXX' as bank, 13232 as account
  union all
 select 'YYY' as bank, 138232 as account
  union all
 select 'ZZZ' as bank, 183823 as account) banks
where balances.bank = banks.bank
  and balances.account = banks.account

select bank, account, sum(amount)
from balances
where (bank = 'XXX' and account = 123i832)
    or (bank = 'YYY' and account = 28323)
    or (bank = 'ZZZ' and account = 2839283)