SQL：查找在'Adam Rivera'拥有账户的所有分支中拥有账户的客户

Question

我需要查找拥有账户的客户的客户ID，姓氏和出生日期所有分支 Adam里维拉拥有账户

这是银行的模式：

§ 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' 

分支号码列表客户已在亚当·里维拉的分支机构的帐户，该reuslt是：

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) 

使用NOT EXIST WHERE后，应返回客户谁拥有所有亚当·里维拉的账户

这是我写的查询。

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计算每个客户不同分支的数量，并检查该数字是否与Rivera先生的电话号码相符。