mysql - 从用户、帖子和评论表中获取所有带有评论数和用户的帖子

Question

我有 3 个用户、帖子和评论表。用户表字段是

身份证、姓名、性别、电话

posts 表字段是

post_id, user_id, posts, time

评论表字段是

id, user_id, 评论, post_id, 时间

我想获取这些字段的值

姓名、性别、帖子、时间、计数(评论)

Answer 1

SELECT
    x.*, CONCAT(u.FirstName, ' ', u.LastName) AS name, u.Gender AS gender 
FROM (
    SELECT
        p.id , p.post, p.date_time, p.user_id AS uid, COUNT(c.post_id) AS 'count'
    FROM
        posts p
    LEFT JOIN
        comments c ON p.id = c.post_id
    GROUP BY
        p.id
) x 
LEFT JOIN
    users u ON u.id = uid