php - 我如何修复子查询返回多于 1 行的错误

Question

我正在执行这个查询

$sql=mysql_query("SELECT c.*,
  (SELECT Count(b.text) From tbl_people_comment  Where b.idsms=c.id AND b.visible=1) AS ccomment
FROM tbl_peoplesms AS c
INNER JOIN tbl_people_comment AS b ON b.idsms= c.id
WHERE c.visible=1
GROUP BY c.id");

我试图让它在 php 中返回类似的东西

 [
      {
      "id":"20", 
      "name":"test",
      "text":"test",
      "visible":"1",
      "ccomment":"5"
      },

      {
      "id":"19",
      "name":"test",
      "text":"test",
      "visible":"1"
      "ccomment":"7" 
      }
    ]

我用这段代码来展示它

while($row=mysql_fetch_assoc($sql)) 
$output[]=$row; 
print(json_encode($output, JSON_UNESCAPED_UNICODE));

但是，我收到错误子查询返回超过 1 行。

这是 tbl_peoplesms tbl_peoplesms

和 tbl_people_comment tbl_people_comment

Answer 1

不使用内联子查询，而是将 join tbl_people_comment 留在 tbl_peoplesms 上并将 b.visible=1 条件移动到 join 子句中:

SELECT c.name, count(b.text) as ccomment
FROM tbl_peoplesms AS c
LEFT JOIN tbl_people_comment AS b ON b.idsms= c.id and b.visible=1
GROUP BY c.name

如果您想在选择列表中包含更多来自 tbl_people_comment 表的字段，那么也将它们添加到 group by 子句中。