用左联接计数行
用左联接计数行
提问于 2017-07-17 10:28:58
我在用左联接计数行时有问题。所以我有两个表(域，聊天)，我想提取每个域的聊天总数，开放聊天的数量和关闭聊天的数量。公共列是“域”。
这是我的代码：
$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
有人能帮我吗？我不明白问题在哪里..。
非常感谢!
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-07-17 10:34:11
试试下面的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));Stack Overflow用户
发布于 2017-07-17 10:31:49
相反，使用单个联接。
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.domainStack Overflow用户
发布于 2017-07-17 10:33:54
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45141814
复制相关文章
相似问题
腾讯云开发者
