SQL：查找在"Adam Rivera"拥有帐户的所有分支机构中拥有帐户的客户

Question

我需要查找在 Adam Rivera 拥有帐户的所有分支机构中拥有帐户的客户 ID、姓氏和出生日期。

这是银行架构：

§ Customer = {customerID, firstName, lastName, income, birthDate }
§ Account = {accNumber, type, balance, branchNumberFK-Branch}
§ Owns = {customerIDFK-Customer, accNumberFK-Account}
§ Transactions = {transNumber, accNumberFK-Account, amount}
§ Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch}
§ Branch = {branchNumber, branchName, managerSINFK-Employee, budget}

亚当·里维拉拥有的所有分支编号，查询给出：

branchNumber
1
4
SELECT b2.branchNumber -- all branches that Adam Rivera owns
FROM Customer c2 join Owns o2 on c2.customerID = o2.customerID join Account a2 on a2.accNumber = o2.accNumber join Branch b2 on b2.branchNumber = a2.branchNumber
WHERE c2.firstName = 'Adam' and c2.lastName = 'Rivera'

客户在亚当·里维拉的分行拥有帐户的分行号码列表，重用是：

cusID   lstName birthDate   branch#
10839   Hayes   1977-06-09  1
10839   Hayes   1977-06-09  4
11790   Green   1920-11-19  1
11790   Green   1920-11-19  1
13230   Brooks  1967-05-04  1
13423   Simmons 1955-03-21  1
13423   Simmons 1955-03-21  4
13697   Hill    1951-03-07  1
13697   Hill    1951-03-07  4
14295   Ramirez 1983-06-30  4
18166   Barnes  1951-03-07  1
19973   Kelly   1987-07-15  1
22050   Sanchez 1935-01-08  1
.......

SELECT c1.customerID, c1.lastName, c1.birthDate, b1.branchNumber
FROM Customer c1 join Owns o1 on c1.customerID = o1.customerID join Account a1 on a1.accNumber = o1.accNumber join Branch b1 on b1.branchNumber = a1.branchNumber
WHERE EXISTS 
      SELECT b3.branchNumber
      FROM Customer c3 join Owns o3 on c3.customerID = o3.customerID join Account a3 on a3.accNumber = o3.accNumber join Branch b3 on b3.branchNumber = a3.branchNumber
      WHERE c3.firstName = 'Adam' and c3.lastName = 'Rivera' and b1.branchNumber = b3.branchNumber 

以下结果应返回客户没有帐户的所有分支机构

，如果客户在每个分支机构都有一个帐户，则此结果为空。

WHERE NOT EXISTS
    (branch numbers that Adam Rivera owns) 
    EXCEPT 
    (a list of branch numbers that a customer has account at Adam Rivera's branches) 

在 WHERE 之后使用不存在，应该返回拥有所有 Adam Rivera 帐户的客户

这是我写的查询。

SELECT c1.customerID, c1.lastName, c1.birthDate
FROM Customer c1 join Owns o1 on c1.customerID = o1.customerID join Account 
a1 on a1.accNumber = o1.accNumber join Branch b1 on b1.branchNumber = 
a1.branchNumber
WHERE NOT EXISTS
  (-- all branch numbers that Adam Rivera owns
  (SELECT b2.branchNumber 
  FROM Customer c2 join Owns o2 on c2.customerID = o2.customerID join 
Account a2 on a2.accNumber = o2.accNumber join Branch b2 on b2.branchNumber = a2.branchNumber
  WHERE c2.firstName = 'Adam' and c2.lastName = 'Rivera') 
  EXCEPT
  -- a list of branch numbers that a customer has account at Adam Rivera's branches
  (SELECT b3.branchNumber 
  FROM (Customer c3 join Owns o3 on c3.customerID = o3.customerID join Account a3 on a3.accNumber = o3.accNumber join Branch b3 on b3.branchNumber = a3.branchNumber)
  WHERE c3.firstName = 'Adam' and c3.lastName = 'Rivera' and b1.branchNumber = b3.branchNumber )
  )

查询应返回 Hayes、Simmons、Hill。但结果不返回任何内容（错误）。我无法弄清楚这里出了什么问题。

Answer 1

这里有一种方法：

with ar as (
      select distinct a.branchNumber
      from Customer c join
           Owns o 
           on c.customerID = o.customerID join
           Account a
           on a.accNumber = o.accNumber 
      where c.firstName = 'Adam' and c.lastName = 'Rivera'
     )
select o.customerId
from Owns o 
     Account a
     on a.accNumber = o.accNumber join
     ar
     on ar.branchNumber = a.branchNumber
group by o.customerId
having count(distinct a.branchNumber) = (select count(*) from ar);

子查询是里维拉先生拥有的帐户。 join与每个帐户匹配。 group by和having计算每个客户的不同分行的数量，并检查该号码是否与里维拉先生的号码相匹配。