mysql - 3 Mysql 内连接，最后一个连接是 ORDER BY 子句

Question

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

$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;