mysql里有三个表,分别是
表 topic (话题)
id name userId
1 话题1 1
2 话题2 2
3 话题3 1表 restore(话题回复)
id topicId
1 1
2 1
3 2
4 1
5 2表 user (用户)
id userName
1 tom
2 jack这三张表,我要取出每条topic表的名字和每个topic的回复数目以及每个topic的作者。这样的sql语句怎么写。
表 topic (话题)
id name userId
1 话题1 1
2 话题2 2
3 话题3 1表 restore(话题回复)
id topicId
1 1
2 1
3 2
4 1
5 2表 user (用户)
id userName
1 tom
2 jack这三张表,我要取出每条topic表的名字和每个topic的回复数目以及每个topic的作者。这样的sql语句怎么写。
FROM `dszg_publicdiscusstopic` a
INNER JOIN (SELECT topicId, count( * ) AS total
FROM `dszg_publicdiscusscon`
GROUP BY `dszg_publicdiscusscon`.`topicId`
)b ON a.`publicDiscussTopicId` = b.topicId
LEFT JOIN `dszg_user` c ON a.userId = c.userId我这样写了下 好像只出来两个话题,那个第三条话题因为没有人回复,竟然选不出来
SELECT name, count( topicId ) AS total, userName
FROM topic
LEFT JOIN
restore ON topic.id = topicId
INNER JOIN user ON userId = user.id
GROUP BY name, topicId, userName