Mysql内部连接，最后一个连接是ORDER BY子句

Question

我有3个表，我试图内连接(雄心勃勃的我知道)。连接的第一个查询基本上只是查询我的成员表，以管道到第二个查询，第二个查询是实际保存这些成员的帖子的帖子表(用户通过成员信息搜索以查看他们的帖子)。第三个也是最后一个查询是简单地按浏览次数最多的帖子的频率排序。我有这两个查询分别工作:

$sql_string = "
SELECT m.id
     , m.username
     , m.gender
     , p.* 
  FROM members m
  JOIN posts p
    ON p.member_id = m.id  
 WHERE m.active='y' 
   AND m.gender='M' 
   AND m.city='Los Angeles' 
   AND m.state='California' 
   AND p.active='y';
";

完成前两个查询和最后一个查询:

$sql_string2 = "SELECT post_id FROM post_views GROUP BY post_id ORDER BY COUNT(*) DESC";

完成最后的查询。我只需要把这两个组合起来，但是当我这样做的时候:

$final_sql_string = "SELECT members.id, members.username, members.gender, posts.* FROM members INNER JOIN posts ON members.id = posts.member_id WHERE members.active='y' AND members.gender='M' AND members.city='Los Angeles' AND members.state='California' AND posts.active='y' INNER JOIN post_views ON posts.id = post_views.post_id GROUP BY post_views.post_id ORDER BY COUNT(*) DESC";

我得到一个错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN post_views ON posts.id = post_views.post_id GROUP BY post_views.post_' at line 1

任何想法?以下是我的表格，有兴趣的可以参考:

CREATE TABLE IF NOT EXISTS `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) NOT NULL,
 `age` varchar(3) NOT NULL,
 `gender` varchar(1) NOT NULL,
 `city` varchar(20) NOT NULL,
 `state` varchar(50) NOT NULL,
 `active` enum('y','n') NOT NULL DEFAULT 'y',
 `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `member_id` int(11) NOT NULL,
 `title` text NOT NULL,
 `comments` enum('y','n') NOT NULL DEFAULT 'y',
 `post_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `list_weight` double NOT NULL,
 `active` enum('y','n') NOT NULL DEFAULT 'y',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=47 ;
CREATE TABLE IF NOT EXISTS `post_views` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `post_id` int(11) NOT NULL,
 `member_id` int(11) NOT NULL,
 `post_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=287 ;

Answer 1

下面应该可以工作:

SELECT m.id
     , m.username
     , m.gender
     , p.* 
     , pc.post_count
  FROM members m
  JOIN posts p
    ON p.member_id = m.id
  LEFT JOIN (
    SELECT post_id, COUNT(*) post_count FROM post_views GROUP BY post_id
    ) pc ON p.id = pc.post_id 
 WHERE m.active='y' 
   AND m.gender='M' 
   AND m.city='Los Angeles' 
   AND m.state='California' 
   AND p.active='y'
 ORDER BY post_count DESC;

"加入"你收集到的计数并按它们排序。

如果你想保持你的"风格"，你可以使用:

SELECT m.id
     , m.username
     , m.gender
     , p.* 
  FROM members m
  JOIN posts p
    ON p.member_id = m.id
 WHERE m.active='y' 
   AND m.gender='M' 
   AND m.city='Los Angeles' 
   AND m.state='California' 
   AND p.active='y'
 ORDER BY (SELECT COUNT(*) FROM post_views WHERE post_id = p.id) DESC;