内部联接查询建议

如果不知道表格的确切结构,就很难判断。假设你的关注者表看起来像这样

   blogid | userid
   -------+----------
     1    |  2
   -------+----------
     1    |  1
   -------+----------
     1    |  3
   -------+----------
     2    |  2
   -------+---------

 SELECT blogid, COUNT(userid) AS UserCount FROM followers 
 GROUP BY blogid ORDER BY UserCount DESC;

然后,您可以加入博客表,从该表中获取所需的列。

SELECT blogs.*, IFNULL(f.UserCount,0) AS UserCount
FROM blogs 
 LEFT JOIN (
    SELECT blogid, COUNT(userid) AS UserCount FROM followers 
    GROUP BY blogid
 ) f
 ON f.blogid = blogs.id
 ORDER BY UserCount DESC

我建议稍微调整一下Mihai的建议,所以在查询的末尾添加这个字符串: GROUP BY blogs.id ORDER BY COUNT(userid) DESC

$query = "SELECT blogs.id, count(*) AS total FROM blogs INNER JOIN followers ON (blogs.id = followers.blogid) WHERE followers.userid='" .$usernamesesh. "'" GROUP BY blogs.id ORDER BY total;

希望这能有所帮助

您可以根据子查询加入:

SELECT blogs.*, s.cnt
FROM blogs 
     LEFT JOIN (SELECT blogid, count(*) as cnt 
                FROM followers
                GROUP BY blogid) s
     ON s.blogid = blogs.id
ORDER BY s.cnt

编辑

具有

CREATE TABLE blogs (id);
create TABLE followers(blogid);
INSERT into blogs values (1),(2),(3);
INSERT into followers values (1),(1),(3);

您可以:

id          cnt
----------  ----------
2
3           1
1           2