after 4 days of tries and researchs over the web I can't make my wishes working.
Any help would be appreciated !
Performed on MYSQL WORKBENCH
I got a DB made of 3 tables :
user | Posts | likes |
---|---|---|
username | id | id |
id | title | userId (foreign key from user.id) |
content | postId (foreign key from posts.id) | |
password | date | |
image | imageUrl | |
userId (foreign key from user.id) |
我想要一个查询,将采取从用户和帖子的具体数据,加上总计数的喜欢为每一个帖子的id排序后,如果没有喜欢这个id存在的总结果为0.
- 在用户和帖子中 Select 具体数据;( working )
- 计算每个帖子的赞总数,如果任何帖子没有赞,给我一个0;
我的实际问题是:
SELECT P.id, P.userId, P.title, P.content, P.date, P.imageUrl, U.username, U.permission, U.image,
COUNT(CASE WHEN L.postId = P.id THEN 1 ELSE NULL END) AS totalLikes
FROM posts AS P
LEFT JOIN user AS U
ON U.id = P.userId
LEFT JOIN likes AS L
ON P.id = L.postId
ORDER BY P.id DESC;
这篇文章只返回了一篇文章,包含了数据库中所有喜欢的内容,所以这不是我所期望的:-(