gpt4 book ai didi

mySQL 多重 INNER JOIN

转载 作者:可可西里 更新时间:2023-11-01 07:54:49 25 4
gpt4 key购买 nike

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

三个表是:

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 和记录进行计数。我们尝试了以下查询,但发现它出于某种原因使 totalClients 的数量成倍增加:

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

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

最佳答案

你需要这样的东西:

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

关于mySQL 多重 INNER JOIN,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4503030/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com