我正在努力获得一个项目中最受欢迎的用户列表。受欢迎的标准是:a)帖子最多,b)帖子有最多的赞,c)帖子被分享最多,d)博客有最多的订阅者。
我可以成功地使标准a)和d)工作良好,像这样:
SELECT User.id, User.first_name, COALESCE(Sub.subs, 0) AS subscribers, COALESCE(Post.posts, 0) AS posts, User.profile_pic
FROM blogs AS Blog
LEFT JOIN (
SELECT user_id, count(1) as subs
FROM subscriptions
GROUP BY user_id) AS Sub ON (Sub.blog_id = Blog.id)
LEFT JOIN (
SELECT user_id, count(1) as posts
FROM posts
GROUP BY user_id) AS Post ON (Post.blog_id = Blog.id)
INNER JOIN users AS User ON (User.id = Blog.user_id)
ORDER BY subscribers DESC, posts DESC
LIMIT 25
这给了我前25个用户的列表,他们的博客有最多的帖子和最多的订阅者。然而,我不确定如何将条件b)和c)合并到这个查询中,这可能会涉及子查询中的JOINS
,基于下面的表结构:
---------------------------------
| id | blog_name | user_id |
---------------------------------
Posts表:
------------------------------
| id | user_id | blog_id |
------------------------------
订阅表:
-----------------------------
| id | user_id | blog_id |
-----------------------------
Users表:
-------------------------------------------------------------
| id | first_name | last_name | enc_pwd | profile_pic |
-------------------------------------------------------------
喜欢表:
-----------------------------
| id | post_id | user_id |
-----------------------------
股票表:
----------------------------------------
| id | from_user | to_user | post_id |
----------------------------------------
我已经尝试了以下查询,但它给了我一个Unknown column Post.id in ON clause
错误。
SELECT User.id, User.first_name, COALESCE(Sub.subs, 0) AS subscribers, COALESCE(Post.posts, 0) AS posts, COALESCE(Share.shares, 0) AS shares, User.profile_pic
FROM blogs AS Blog
LEFT JOIN (
SELECT user_id, count(1) as subs
FROM subscriptions
GROUP BY user_id) AS Sub ON (Sub.blog_id = Blog.id)
LEFT JOIN (
SELECT user_id, count(1) as posts
FROM posts
GROUP BY user_id) AS Post ON (Post.blog_id = Blog.id)
LEFT JOIN (
SELECT s.post_id, count(1) as shares
FROM shares AS s
INNER JOIN posts AS p ON p.id = s.post_id
INNER JOIN users AS U ON u.id = p.user_id
GROUP by s.post_id) AS Share ON (Share.post_id = Post.id) -- Error occurs here
INNER JOIN users AS User ON (User.id = Blog.user_id)
ORDER BY subscribers DESC, posts DESC
LIMIT 25
经过一番尝试和错误,我终于弄明白了。在我看来,您必须以这样一种方式编写查询,即所有子查询最终都应引用FROM
子句(在本例中为x.blog_id = Blog.id
)中第一个表中的相关列。我是一个中级MySQL用户,这是我第一次不得不编写像这样的怪物查询(我知道这与所有真正的怪物SQL还差得远!),所以请原谅我无法在我的回答中使用正确的SQL术语。我一直很高兴CakePHP和Rails为我编写查询。所以,如果你们中的任何一个SQL大师遇到这个问题,如果你能更技术地解释一下,那就太好了。
下面是我如何得到前25名用户的列表,他们的帖子最多,被点赞最多,分享最多,订阅者最多:
SELECT User.id, User.first_name, COALESCE(Sub.subs, 0) AS subscribers, COALESCE(Post.posts, 0) AS posts, COALESCE(Share.shares, 0) AS shares, COALESCE(`Like`.likes, 0) AS likes, User.profile_pic
FROM blogs AS Blog
LEFT JOIN (
SELECT blog_id, count(1) as subs
FROM subscriptions
GROUP BY blog_id) AS Sub ON (Sub.blog_id = Blog.id)
LEFT JOIN (
SELECT blog_id, count(1) as posts
FROM posts
GROUP BY blog_id) AS Post ON (Post.blog_id = Blog.id)
LEFT JOIN (
SELECT blog_id, count(1) as shares
FROM blogs
INNER JOIN posts ON (posts.blog_id = blogs.user_id)
INNER JOIN shares ON (posts.id = shares.post_id)
GROUP by blog_id) AS Share ON (Share.blog_id = Blog.id)
LEFT JOIN (
SELECT blog_id, count(1) as likes
FROM blogs
INNER JOIN posts ON (posts.blog_id = blogs.user_id)
INNER JOIN likes ON (likes.post_id = posts.id)
GROUP BY blog_id) AS `Like` ON (`Like`.blog_id = Blog.id)
INNER JOIN users AS User ON (User.id = Blog.user_id)
ORDER BY subscribers DESC, posts DESC, shares DESC, likes DESC
LIMIT 25
导致(例如):
-------------------------------------------------------------------------------------------------
| id | first_name | last_name | subscribers | posts | shares | likes | profile_pic |
-------------------------------------------------------------------------------------------------
| 1 | Yss | Salas | 243 | 433 | 154 | 545 | 1437ytr_o.png |
| 4 | Balu | V | 143 | 145 | 45 | 234 | default.png |
| 3 | Jessica | Nigri | 134 | 120 | 90 | 77 | default.png |
-------------------------------------------------------------------------------------------------