我正在执行此查询
$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_peoplesmstbl_peoplesms
和tbl_people_commenttbl_people_comment
不使用内联子查询，只需在tbl_peoplesms上留下join tbl_pepeople_comment，并将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表中的更多字段，那么也可以将它们添加到groupby子句中。