mysql - 吸引所有贷款的客户

这是一个家庭作业问题。我想找到所有贷款的客户。下表如下。

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

Sample tables:
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

这里的答案是汤姆。
现在，出于学习的目的，我的要求是不允许我计算贷款的总数，并将借款人的贷款数量与之进行比较，如果我可以使用它，这完全有效。
所以我所想的是看到所有借款人的贷款都存在于贷款表中。我试着做双重否定，首先得到的客户不是所有的贷款，但这不起作用。我该怎么做呢？考虑到我不能做的事，这是最好的方法吗？
我觉得事情没有看上去那么复杂，但是我自己想不出来。

最佳答案

LiveDemo

CREATE TABLE #loan(
   number VARCHAR(7) NOT NULL
  ,type   VARCHAR(8) NOT NULL
);
INSERT INTO #loan(number,type) VALUES ('L1','student');
INSERT INTO #loan(number,type) VALUES ('L2','car');
INSERT INTO #loan(number,type) VALUES ('L3','house');
INSERT INTO #loan(number,type) VALUES ('L4','car');
INSERT INTO #loan(number,type) VALUES ('L5','car');


CREATE TABLE #borrower(
   cust  VARCHAR(7) NOT NULL
  ,no VARCHAR(8) NOT NULL);

INSERT INTO #borrower VALUES ('Jim','L2'), ('Tom','L1'), ('Tom','L2'), ('Tom','L3'),
                             ('Tom','L4'), ('Tom','L5'), ('Bob','L3');

SELECT cust
FROM (
  SELECT b.cust, COUNT(DISTINCT type) AS c
  FROM #loan l
  JOIN #borrower b
    ON l.number = b.no
    GROUP BY b.cust) AS s
WHERE s.c = (SELECT COUNT(DISTINCT type) FROM #loan);

编辑：
LiveDemo2
不需要计数：

SELECT main.cust
FROM (SELECT DISTINCT cust FROM #borrower) AS main
LEFT JOIN (
  SELECT DISTINCT sub2.cust
  FROM (
    SELECT DISTINCT cust, type
    FROM #borrower b
    JOIN #loan l
      ON b.no = l.number) as sub
    RIGHT JOIN (
    SELECT b.cust, s.type
    FROM #borrower b
    CROSS JOIN (SELECT DISTINCT type FROM #loan) AS s) AS sub2
      ON sub.cust = sub2.cust AND sub.type = sub2.type
    WHERE sub.cust IS NULL
) AS sub3
  ON main.cust = sub3.cust
WHERE sub3.cust IS NULL;

工作原理：

SELECT DISTINCT sub2.cust
  FROM (
SELECT DISTINCT cust, type
FROM #borrower b
JOIN #loan l
  ON b.no = l.number) as sub
RIGHT JOIN (
    SELECT b.cust, s.type
    FROM #borrower b
    CROSS JOIN (SELECT DISTINCT type FROM #loan) AS s) AS sub2
  ON sub.cust = sub2.cust AND sub.type = sub2.type
WHERE sub.cust IS NULL;

它将归还那些还没有全部贷款的客户。
然后你就把它们从所有的顾客身上拿走。
编辑2：
使用EXCEPT（SQL Server）：
LiveDemo3

SELECT main.cust
FROM #borrower AS main
EXCEPT
(
  SELECT cust
  FROM (
   SELECT b.cust, s.type
   FROM #borrower b
   CROSS JOIN (SELECT DISTINCT type FROM #loan) AS s
   EXCEPT
   SELECT cust, type
   FROM #borrower b
   JOIN #loan l
     ON b.no = l.number ) AS sub
)

关于mysql - 吸引所有贷款的客户，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33159666/