php - SQL 中的连接数

Question

我有两个表(post.id 是主键，在 like.post_id 中成为次键)

表“帖子”

id      user_id     image
1       10          abc.jpg
2       20          xyz.jpg
3       10          ajb.jpg

表“赞”

id      user_id     post_id         likes
1       10          1               1
2       20          2               1
3       10          1               1
4       10          1               1
3       10          3               1

现在我想每当我传递 user_id 时我都想获得所有用户的帖子以及帖子的点赞数

我尝试了以下代码但没有成功，

SELECT selfie_info.id,selfie_info.user_id,selfie_info.image, (SELECT COUNT(m.likes)FROM post_likes m WHERE m.user_id='10') as total_likes FROM selfie_info where user_id='10'

我该怎么做？我想要如下结果(如果我通过 user_id=10 )

user_id     post_id     likes
10          1           3
10          3           1

Answer 1

SELECT p.user_id, p.id AS post_id, COUNT(l.id) AS total_likes
FROM post p
LEFT JOIN likes l ON l.post_id =p.id
WHERE p.user_id=10 GROUP BY p.id;