mySQL 多重 INNER JOIN

Question

我们想要查询三个表，以找出每个企业的客户数量以及每个企业的记录数

这三个表是：

businessDetails
-------------------
businessDetails.ID
businessDetails.name

clientDetails
-------------------
clientDetails.ID
clientDetails.businessDetailsID

records
-------------------
records.ID
records.businessDetailsID

我们可以从其中两个表中选择计数，这没有任何问题一次（businessDetails 加上 clientDetails 或记录）。例如，

SELECT  businessDetails.name AS businessName
        , COUNT(clientDetails.businessDetailsID) AS totalClients
FROM `businessDetails`
INNER JOIN clientDetails 
        ON clientDetails.businessDetailsID = businessDetails.businessDetailsID
GROUP BY 
businessDetails.name
ORDER BY
totalClients DESC

此查询为我们提供了预期的良好结果：

--------------------------------
businessName    |   totalClients
--------------------------------
Initech         |   23
Cylon Inc       |   148
The Dude Ltd    |   71

我们遇到的问题是如何同时对 clientDetails 和记录进行计数。我们尝试了以下查询，但发现它由于某种原因使总客户端数成倍增加：

SELECT  businessDetails.name AS businessName
        , COUNT(clientDetails.businessDetailsID) AS totalClients
        , COUNT(records.businessDetailsID) AS totalRecords
FROM `businessDetails`
INNER JOIN clientDetails 
        ON clientDetails.businessDetailsID = businessDetails.businessDetailsID
INNER JOIN records ON records.businessDetailsID = businessDetails.ID
GROUP BY 
businessDetails.name
ORDER BY
totalClients DESC

这返回的结果类似于：

--------------------------------------------------------
businessName    |   totalClients    |   totalRecords
--------------------------------------------------------
Initech         |   93              |    93
Cylon Inc       |   398             |    398
The Dude Ltd    |   215             |    215

我希望我们只是犯了一些简单的错误。任何帮助将不胜感激。

Answer 1

你需要这样的东西：

SELECT  
    businessDetails.name AS businessName,
    (SELECT count('x') FROM clientDetails WHERE clientDetails.businessDetailsID = businessDetails.businessDetailsID) as totalclients,
    COUNT(records.businessDetailsID) AS totalRecords
FROM 
    `businessDetails`
    INNER JOIN records ON records.businessDetailsID = businessDetails.ID
GROUP BY 
    businessDetails.name
ORDER BY
    totalClients DESC

Answer 2

如果您像这样添加一个DISTINCT 怎么样：

SELECT  businessDetails.name AS businessName
        , COUNT(distinct clientDetails.ID) AS totalClients
        , COUNT(distinct records.ID) AS totalRecords
FROM `businessDetails`
INNER JOIN clientDetails 
        ON clientDetails.businessDetailsID = businessDetails.businessDetailsID
INNER JOIN records ON records.businessDetailsID = businessDetails.ID
GROUP BY 
businessDetails.name
ORDER BY
totalClients DESC

这样，对于每组businessDetails.name，您都可以在该“窗口”内获得不同clientDetails 和记录的计数。

Answer 3

计数（不同[FieldName]） 在 MYSQL 中工作。尝试一下。

SELECT  businessDetails.name AS businessName
        , COUNT(Distinct clientDetails.businessDetailsID) AS totalClients
        , COUNT(records.businessDetailsID) AS totalRecords
FROM `businessDetails`
INNER JOIN clientDetails 
        ON clientDetails.businessDetailsID = businessDetails.businessDetailsID
INNER JOIN records ON records.businessDetailsID = businessDetails.ID
GROUP BY 
businessDetails.name
ORDER BY
totalClients DESC