SQL查询需求：筛选同时持有支票与储蓄账户的客户账户信息

问题背景 #

需要编写SQL查询，返回**同时拥有至少一个支票账户（type='chq'）和至少一个储蓄账户（type='sav'）**的客户信息，包括Customer ID、账户类型、账号及余额，结果按Customer ID、账户类型、账号排序。

数据库Schema #

Account = {accNumber, type, balance, branchNumberBranch}
Owns = {customerIDCustomer, accNumberAccount}
Transactions = {transNumber, accNumberAccount, amount}
Employee = {sin, firstName, lastName, salary, branchNumberBranch}
Branch = {branchNumber, branchName, managerSINEmployee, budget}

现有问题 #

当前使用UNION拼接两种账户的查询结果，会包含仅持有单一类型账户的客户，无法满足“同时拥有两种账户”的要求；而INTERSECT无法使用，因为单个账户不可能同时属于两种类型。

解决方案 #

核心逻辑是先筛选出同时持有两种账户的客户ID集合，再关联查询这些客户的对应账户信息，以下是两种可行实现：

方法1：分组统计筛选客户ID #

通过分组统计每个客户的账户类型数量，筛选出同时拥有chq和sav的客户，再关联获取账户详情：

SELECT 
    C.customerID, 
    A.type, 
    A.accNumber, 
    A.balance
FROM 
    Customer C
JOIN 
    Owns O ON C.customerID = O.customerIDCustomer
JOIN 
    Account A ON O.accNumberAccount = A.accNumber
WHERE 
    C.customerID IN (
        SELECT 
            O.customerIDCustomer
        FROM 
            Owns O
        JOIN 
            Account A ON O.accNumberAccount = A.accNumber
        WHERE 
            A.type IN ('chq', 'sav')
        GROUP BY 
            O.customerIDCustomer
        HAVING 
            COUNT(DISTINCT A.type) = 2
    )
    AND A.type IN ('chq', 'sav')
ORDER BY 
    C.customerID, 
    A.type, 
    A.accNumber;

方法2：EXISTS子查询验证账户存在性 #

通过两个EXISTS子查询分别验证客户是否持有chq和sav账户，再获取对应账户信息：

SELECT 
    C.customerID, 
    A.type, 
    A.accNumber, 
    A.balance
FROM 
    Customer C
JOIN 
    Owns O ON C.customerID = O.customerIDCustomer
JOIN 
    Account A ON O.accNumberAccount = A.accNumber
WHERE 
    EXISTS (
        SELECT 1
        FROM Owns O1
        JOIN Account A1 ON O1.accNumberAccount = A1.accNumber
        WHERE O1.customerIDCustomer = C.customerID AND A1.type = 'chq'
    )
    AND EXISTS (
        SELECT 1
        FROM Owns O2
        JOIN Account A2 ON O2.accNumberAccount = A2.accNumber
        WHERE O2.customerIDCustomer = C.customerID AND A2.type = 'sav'
    )
    AND A.type IN ('chq', 'sav')
ORDER BY 
    C.customerID, 
    A.type, 
    A.accNumber;

关键说明 #

• 两种方法都先锁定符合条件的客户，避免了UNION引入的无关数据。
• EXISTS方式在数据量较大时性能更优，因为它找到匹配记录后立即停止查询，无需分组统计。
• 建议使用显式JOIN替代旧的逗号分隔表写法，逻辑更清晰，符合现代SQL规范。

内容的提问来源于stack exchange，提问作者Hoang My