mysql - 将嵌套选择转换为加入

Question

SELECT bank.bank_name, 
   (SELECT COALESCE(SUM(account.balance),0) 
   FROM account 
   WHERE account.bank_name = bank.bank_name 
       AND account.account_type = 'loan') AS total 
FROM bank

账户表：

标签： mysqlsql

Answer 1

您的查询可能比join/group by版本更有效，但它相当于：

SELECT b.bank_name, COALESCE(SUM(a.balance), 0) as total 
FROM bank b LEFT JOIN
     account a
     ON a.bank_name = b.bank_name AND a.account_type = 'loan'
GROUP BY b.bank_name;

我应该指出，如果所有银行都有贷款（这似乎是合理的），则没有JOIN必要：

SELECT a.bank_name, SUM(a.balance as total 
FROM account a
WHERE a.account_type = 'loan'
GROUP BY a.bank_name;