sql - 使用 NOT EXISTS 接受所有贷款的借款人

Question

我想找到接受所有贷款类型的借款人。

架构:

loan (number (PKEY), type, min_rating)
borrower (cust (PKEY), no (PKEY))

示例表:

number | type     | min_rating
------------------------------
L1     | student  | 500
L2     | car      | 550
L3     | house    | 500
L4     | car      | 700
L5     | car      | 900

cust  | no 
-----------
Jim   | L2
Tom   | L1
Tom   | L2
Tom   | L3
Tom   | L4
Tom   | L5
Bob   | L3

这里的答案是“汤姆”。

我可以简单地计算贷款总数并将借款人的贷款数量与其进行比较，但出于此作业和学习的目的，我不允许这样做(这是一项家庭作业)。

我想使用双重否定，首先找到没有接受所有贷款的借款人，然后找到不在该集合中的借款人。我想使用 NOT EXISTS 的嵌套，在这里我首先找到没有接受所有贷款的借款人，但我无法为此创建有效的查询。

Answer 1

一个简单的方法是使用事实:

• 当没有连接时，外连接会给你空值
• coalesce() 可以将 null 变成空白(总是小于实际值)

因此，没有所有贷款类型的人的最小合并贷款数将为空白:

select cust
from borrower b
left join loan l on l.number = b.no
group by cust
having min(coalesce(l.number, '')) > ''

group-by 巧妙地回避了多次选择人员的问题(以及经常需要的丑陋的子查询)，并依赖于贷款编号永远不会为空的相当合理的假设。即使那是可能的，您仍然可以找到使这种模式起作用的方法(例如，将 min_rating 合并为负数等)。

上面的查询可以重写，可能更易读，使用NOT IN表达式:

select distinct cust
from borrower
where cust not in (
  select cust
  from borrower b
  left join loan l on l.number = b.no
  where l.number is null
)

利用未命中的连接返回所有空值这一事实，内部查询的 where 子句仅保留未命中 的连接。

您需要使用 DISTINCT 来阻止借款人出现两次。

---

您的模式有问题 - 借款人和负载之间存在多对多关系，但您的模式处理得不好。 borrower 每个人应该有一行，还有一个关联表来记录借款人贷款的事实:

create table borrower (
    id int,
    name varchar(20)
    -- other columns about the person
);

create table borrrower_loan (
    borrower_id int, -- FK to borrower
    load_number char(2) -- FK to loan
);

这意味着您将不需要 distinct 运算符(留给您找出原因)，但也可以处理现实生活中的情况，例如两个借款人同名。