php - SQL 中的联接计数
问题描述
我有两个表(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
解决方案
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;
推荐阅读
- python - 使用正则表达式在列表中查找十六进制值
- java - reading CSV file from s3 using spark
- ios - How can I force/setup the right keyboard in the iOS Simulator during tests?
- c++ - Setting IP address in Linux using c++
- list - MVC ModelState Remove keys when deleting item in a list model
- node.js - 使用 Gitlab 运行 mocha 测试
- angular - 选项卡根元素不可见
- django - 更改 AUTH_USER_MODEL 后,Django Admin 将无法工作
- vba - 更新现有记录并在表中添加新记录 (MS Access VBA)
- angular - Angular 跨域请求被阻止