假设有两个表：
表A.
@H_403_4@messageID / Message / More.. 1 / This is the first message / Etc.. 2 / This is the second message / Etc.. 3 / This is the third message / Etc..表B
@H_403_4@commentID / messageID / Comment 1 / 2 / This is a comment to the second message 2 / 2 / This is another comment to the second message 3 / 3 / This is a comment to the third message表之间的关系是messageID字段.
我想要一个生成这样的结果的查询,其中我从表A中提取所有字段,以及表B中每条消息的注释数计数,如下所示：
@H_403_4@messageID / Message / More... / CommentCount 1 / This is the first message / etc... / 0 2 / This is the second message / etc... / 2 3 / This is the third message / etc... / 1我尝试过这样的事情：
@H_403_4@SELECT tableA.*,count(commentID) as commentcount FROM tableA LEFT JOIN tableB ON tableA.messageID = tableB.messageID GROUP BY messageID