MySQL计数行中的两个单独连接给出了错误的结果

Question

我试图将两个表与我的查询连接在一起，并计算每个连接的匹配行数。不过，它似乎取得了错误的结果。结果似乎使结果翻了一番。这是查询

SELECT *, COUNT(car_id) AS numCars, COUNT(van_id) AS numVans 
            FROM company_branch
            LEFT JOIN car ON car_branchid = company_branch_id
            LEFT JOIN van ON van_branchid = company_branch_id
            WHERE company_branch_userid = 1
            GROUP BY company_branch_id, 
            ORDER BY company_branch_active DESC, company_branch_name ASC

Answer 1

我假设car_id是car表的主要键，而van_id是van表的主要键。在这种情况下，使用DISTINCT单独获取每个组中的计数。尝试以下操作：

SELECT *, COUNT(DISTINCT car_id) AS numCars, COUNT(DISTINCT van_id) AS numVans 
        FROM company_branch
        LEFT JOIN car ON car_branchid = company_branch_id
        LEFT JOIN van ON van_branchid = company_branch_id
        WHERE company_branch_userid = 1
        GROUP BY company_branch_id, 
        ORDER BY company_branch_active DESC, company_branch_name ASC