mysql - 大于一切

Question

查找贷款大于 Brackendale 分行所有贷款的所有客户的姓名。

这样可以吗？

Use University;

SELECT customer_name, loan_number
FROM loan NATURAL JOIN customer
HAVING MAX(amount) > ALL (SELECT sum(loan.amount)
FROM loan 
WHERE branch_name = 'Brakendale');

Answer 1

ALL 是不需要的关键字 - 它什么都不做，因为它是集合的默认关键字(另一个关键字是 ANY)。

将 ALL 排除在外更直接。但是您可能需要一个分组并删除 loan_number，否则每一行都必须在其自己的组中。

这个查询对我来说似乎更有意义:

SELECT customer_name, MAX(amount)
FROM loan
NATURAL JOIN customer
GROUP BY customer_name
HAVING MAX(amount) > (
  SELECT sum(loan.amount)
  FROM loan 
  WHERE branch_name = 'Brakendale');