来自comments
的表结构
id | user_id | cmt_id | slug
1 | 565 | 5 | home
2 | 324 | 6 | home
3 | 71 | 7 | home
4 | 408 | 1 | about
来自cmt_likes
的表结构
id | user_id | cmt_id | slug
1 | 324 | 6 | home
2 | 324 | 6 | home
3 | 324 | 6 | home
4 | 71 | 7 | home
5 | 71 | 7 | home
正如您在表cmt_likes
上看到的,在home
页面上,来自用户324
的评论有3个赞,来自用户71
的评论有2个赞(我使用具有相同cmt_id
和slug
的行数来计算赞)。
这是我当前的sql,这只是为了显示评论:
SELECT
`comments`.`user_id`, `comments`.`cmt`, `comments`.`cmt_id`, `comments`.`slug`, `users`.`username`
FROM `comments`
INNER JOIN `users`
ON `comments`.`user_id` = `users`.`user_id`
WHERE `comments`.`slug` = :slug
ORDER BY `comments`.`id` DESC
但我想要的是ORDER BY
的点赞数量。
所以我试着:
SELECT
`comments`.`user_id`, `comments`.`cmt`, `comments`.`cmt_id`, `comments`.`slug`, `cmt_likes`.`cmt_id`, `users`.`username`
FROM `comments`
INNER JOIN `users`
ON `comments`.`user_id` = `users`.`user_id`
INNER JOIN `cmt_likes`
ON `comments`.`cmt_id` = `cmt_likes`.`cmt_id`
WHERE `comments`.`slug` = :slug
GROUP BY `cmt_likes`.`cmt_id`
ORDER BY `cmt_likes`.`cmt_id` DESC
但是这个sql只返回有'likes‘的行。如果您查看我的表,您将看到来自用户565
的注释没有likes,所以在上面的sql中没有返回此行。
这是我用上面的sql得到的当前结果:
324
71
这就是我所期待的:
324
71
565
发布于 2019-04-25 08:26:45
您需要left join
%s。我还建议使用表别名:
SELECT c.user_id, c.cmt, c.cmt_id, c.slug,
c.cmt_id, u.username
FROM comments c LEFT JOIN
users u
ON c.user_user = u.user_id LEFT JOIN
cmt_likes cl
ON c.cmt_id = cl.cmt_id
WHERE c.slug = :slug
GROUP BY c.user_id, c.cmt, c.cmt_id, c.slug, c.cmt_id, u.username
ORDER BY cl.cmt_id DESC
但是,我认为您还需要在JOIN
之前进行聚合
SELECT c.user_id, c.cmt, c.cmt_id, c.slug,
c.cmt_id, u.username
FROM comments c LEFT JOIN
users u
ON c.user_user = u.user_id LEFT JOIN
(SELECT cl.cmt_id, COUNT(*) as cnt
FROM cmt_likes cl
GROUP BY cl.cmt_id
) cl
ON c.cmt_id = cl.cmt_id
WHERE c.slug = :slug
GROUP BY c.user_id, c.cmt, c.cmt_id, c.slug, c.cmt_id, u.username
ORDER BY cl.cmt_id DESC
https://stackoverflow.com/questions/55840156
复制相似问题