php - Sql计算2个表的总数

Question

我有2个表银行和申请人，如何查询显示所有银行并统计有多少申请人使用该银行

[银行][1]:

| id | Bank_desc    
|----|---------
| 1  | Ambank  
| 2  | Maybank  
| 3  | RHB BANK 
| 4  | OSBC     

[申请人][2]:

| id | Name | Bank|
|----|------|----|
|  1 | JACK |  3 |
|  2 | ANDY |  1 |
|  3 | VOID |  1 |

我想展示这样的东西，[结果][2]:

| id | Bank     |Total|
|----|----------|----|
|  1 | Ambank   |  2 |
| 2  | Maybank  |  0 |
| 3  | RHB BANK |  1 |
| 4  | OSBC     |  0 |

我已经尝试过这个sql

SELECT b.Bank_desc, Count(a.id),
FROM applicant a
LEFT JOIN bank b on b.id= a.bank
GROUP BY b.bank

但结果仅显示有值(value)的银行

[结果][2]:

| id | Bank     |Total|
|----|----------|----|
|  1 | Ambank   |  2 |
| 2  | RHB BANK |  1 |

如有任何帮助，我们将不胜感激，提前致谢!

Answer 1

如果颠倒表的顺序，您当前的查询将起作用:

SELECT
    b.id,
    b.Bank,
    COUNT(a.Bank) AS Total
FROM Bank b
LEFT JOIN Applicant a
    ON b.id = a.Bank
GROUP BY
    b.id,
    b.Bank;

Demo