我有两个表。
我正在尝试从articles获取所有列-状态=0的状态数为CCD_ 2-以及状态＝1的状态数作为status1
"从文章中获取所有内容，并为每个文章行获取状态为0的评论数作为状态0，获取状态为1的评论数为状态1"。可能的
表格：
articles
========
id name
---------
1 abc
2 def
3 ghi
comments
========
id article_id status
-------------------------
1 2 1
2 2 0
3 1 0
4 3 1
文章与状态号组合的预期结果：
id name status0 status1
------------------------------
1 abc 1 0
2 def 1 1
3 ghi 0 1
我使用的是Laravel的Eloquent，但只要看到原始sql语句就足够了。我不知道如何查询和统计这些状态。
多亏了fiddle，我成功地创建了这个查询，但我得到了一个错误：注意（articles = db_surveys和comments = db_answers）
"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`status=1) status1` from `db_surveys` left join `db_answers` on `db_surveys`.`i' at line 1 (SQL: select `db_surveys`.`*, SUM(db_answers`.`status=0) status0, SUM(db_answers`.`status=1) status1` from `db_surveys` left join `db_answers` on `db_surveys`.`id` = `db_answers`.`surveyid` where `db_surveys`.`userid` = 6oGxr)"
完整查询：
"select `db_surveys`.`*, SUM(db_answers`.`status=0) status0, SUM(db_answers`.`status=1) status1` from `db_surveys` left join `db_answers` on `db_surveys`.`id` = `db_answers`.`surveyid` where `db_surveys`.`userid` = 123 group by `db_surveys`.`id`"
**
最终查询：
**
SELECT
`s`.*,
SUM(`a`.`status`='pending') `status0`,
SUM(`a`.`status`='confirmed') `status1`
FROM
`db_surveys` s
LEFT JOIN `db_answers` a
ON `s`.`id` = `a`.`surveyid`
WHERE `s`.`userid` = '6oGxr'
GROUP BY `s`.`id`
您可以将sum（）与表达式一起使用，以根据您的条件获得计数，在sum中使用表达式将导致布尔值o或1
SELECT a.*
,SUM(`status` =0) status0
,SUM(`status` =1) status1
FROM articles a
LEFT JOIN comments c ON(a.id = c.article_id)
GROUP BY a.id
Fiddle演示
编辑在原始查询中，您没有正确使用反勾号
SELECT
`s`.*,
SUM(`a`.`status`=0) `status0`,
SUM(`a`.`status`=1) `status1`
FROM
`db_surveys` s
LEFT JOIN `db_answers` a
ON `s`.`id` = `a`.`surveyid`
WHERE `s`.`userid` = 123
GROUP BY `s`.`id`
可能是这样的：
SELECT a.*, COUNT(c.*) AS status0, COUNT(c2.*) AS status1
FROM articles AS a
LEFT JOIN comments AS c ON c.article_id = a.id AND c.status = 0
LEFT JOIN comments AS c2 ON c.article_id = a.id AND c.status = 1
不是最漂亮的，但它很管用：
SELECT articles.id, articles.name,
(SELECT COUNT(*) FROM comments WHERE article_id = articles.id AND status = 0),
(SELECT COUNT(*) FROM comments WHERE article_id = articles.id AND status = 1)
FROM articles;
http://sqlfiddle.com/#！2/123b68/4