mysql - SQL 计算一个表相对于另一个表的行数

Question

我有 3 个不同的表:

客户端

+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  1 | John      | Doe      |
|  2 | Jane      | Doe      |
+----+-----------+----------+

贷款

+----+--------+-----------+----------------+
| id | amount | client_id | institution_id |
+----+--------+-----------+----------------+
|  1 |    200 |         2 |              3 |
|  2 |    400 |         1 |              1 |
+----+--------+-----------+----------------+

机构

+----+---------------+
| id |     name      |
+----+---------------+
|  1 | Institution A |
|  2 | Institution B |
|  3 | Institution C |
+----+---------------+

我希望创建一个客户在每个机构的贷款数量列表(针对机构表中的每一行)。包括客户在机构的贷款为 0 的情况。

看起来像的东西:

+-----------+-----------+----------+--------------------------+-----------+
| client_id | firstName | lastName | financialInstitutionName | loanCount |
+-----------+-----------+----------+--------------------------+-----------+
|         1 | John      | Doe      | Institution A            |         1 |
|         1 | John      | Doe      | Institution B            |         0 |
|         1 | John      | Doe      | Institution C            |         0 |
|         2 | Jane      | Doe      | Institution A            |         0 |
|         2 | Jane      | Doe      | Institution B            |         0 |
|         2 | Jane      | Doe      | Institution C            |         1 |
+-----------+-----------+----------+--------------------------+-----------+

我已经尝试了所有方式的连接、子查询和 where 子句，但都没有成功。我不理解的概念是如何按机构、按客户(机构总数 x 客户)获得一行。如果没有子查询或联合连接也可以实现该查询，我会很高兴。

感谢您的宝贵时间!

Answer 1

FROM 中的第一个子查询设置该数据，因此每个客户端都有每个机构的记录。然后将其加入计算贷款数量的子查询。

SELECT 
    d.client_id,
    d.firstName,
    d.lastName,
    d.name AS financialInstitutionName,
    CASE WHEN l IS NULL
        THEN 0
        ELSE l.loanCount
    END AS loanCount

FROM
(
    SELECT
        Client.id AS client_id,
        Client.firstName,
        Client.lastName,
        Institution.id AS institution_id,
        Institution.name
    FROM Client, Institution
) AS d

LEFT JOIN (
    SELECT client_id, institution_id, COUNT(id) AS loanCount 
    FROM Loan
    GROUP BY client_id, institution_id
) AS l ON d.client_id = l.client_id AND d.institution_id = l.institution_id

编辑:包括每个机构的记录

编辑:拼写