使用左连接计算行数

Question

我在使用 LEFT JOIN 计算行时遇到问题。 所以我有 2 个表(域、聊天(，我想为每个域提取聊天总数、打开聊天的数量和关闭的聊天数量。公共列是"域">

这是我的代码：

$getDomains = $DB->query("
SELECT 
dom.id as domainID, dom.name as domainName, dom.domain as url, dom.gmt, dom.opening_hours, 
COUNT(t1.id) as chats, 
COUNT(t2.id) as opened_chats, 
COUNT(t3.id) as closed_chats
FROM domains dom
LEFT OUTER JOIN chat t1 ON t1.domain=dom.domain
LEFT OUTER JOIN chat t2 ON t2.domain=dom.domain AND t2.status=1
LEFT OUTER JOIN chat t3 ON t3.domain=dom.domain AND t3.status=3
WHERE dom.account_id = ".$userInfo['account_id']." GROUP BY dom.domain") or die (mysqli_error($DB));

在聊天列中，我有 6 个聊天(3 个打开，3 个关闭(。

而不是：

• 所有聊天： 6
• 打开的聊天： 3
• 封闭式聊天：3

结果是：

• 所有聊天： 54
• 打开的聊天： 54
• 关闭的聊天： 54

有人帮我吗？ 我不明白问题出在哪里...

多谢！

Answer 1

尝试下面的查询而不是LEFT OUTER JOIN，

$getDomains = $DB->query("
SELECT 
dom.id as domainID, dom.name as domainName, dom.domain as url, dom.gmt, dom.opening_hours, 
(select count(t1.id) from chat as t1 where t1.domain=dom.domain) as chats,
(select count(t2.id) from chat as t1 where t2.domain=dom.domain AND t2.status=1) as opened_chats,
(select count(t3.id) from chat as t1 where t3.domain=dom.domain AND t3.status=3) as closed_chats
FROM domains dom
WHERE dom.account_id = ".$userInfo['account_id']." GROUP BY dom.domain") or die (mysqli_error($DB));

Answer 2

SELECT
dom....,
COUNT(*) as chats,
SUM(chat.status=1) as opened_chats,
SUM(chat.status=3) as closed_chats
FROM
domains dom
LEFT JOIN
chat ON chat.domain = dom.domain
WHERE dom.account_id = ? 
GROUP BY dom.domain

Answer 3

改用单个连接

SELECT dom.*, 
COUNT(t.id) as chats, 
sum(t.status=1) as opened_chats, 
sum(t.status=3) as closed_chats
FROM domains dom
LEFT OUTER JOIN chat t ON t.domain=dom.domain