MYSQL:多表连接 - 以先前的连接为条件

Question

    MEMBERS_TABLE

    member_id
    ---------------------------------------------
    1


    ACCOUNTS_TABLE

    account_id  member_id
    ---------------------------------------------
    1               1


    INVESTMENTS_TABLE

    investment_id   account_id
    ---------------------------------------------
    1               1
    2               1


    FUNDS_TABLE

    fund_id     investment_id
    ---------------------------------------------
    1               1
    2               2

这是我当前的查询:

SELECT 
    m.member_id,
    a.account_id, 
    i.investment_id, 
    f.fund_id, 
    COUNT(a.account_id) AS member_accounts_total, 
    COUNT(i.investment_id) AS member_investments_total, 
    COUNT(f.fund_id) AS member_funds_total 
FROM members AS m
    LEFT JOIN accounts AS a ON m.member_id = a.member_id
    LEFT JOIN investments AS i ON a.account_id = i.account_id
    LEFT JOIN funds AS f ON f.fund_id = i.fund_id

我希望看到以下结果:
member_accounts_total: 1
member_investments_total: 2
member_funds_total:2

相反，我得到了这些结果:
member_accounts_total: 2
member_investments_total: 2
member_funds_total:2

我真的不想为此编写多个查询。

Answer 1

只需要改变
COUNT(a.account_id) AS member_accounts_total，

到
COUNT(不同的 a.account_id) AS member_accounts_total,

你得到 2 的原因是因为左连接账户到投资导致 2 条记录。要获得不同的成员数量，您需要添加 well...distinct。

请注意，您可能还会遇到其他总数的问题(从长远来看，可能也需要 Distinct ...)，比如一个成员是否有多个帐户。您也可能会得到奇怪的计数(如果每个账户都有相同的投资……您希望只看到计数一次还是两次？