有这样两张表 post 和 postmeta。

表 post 结构如下

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
)

表 postmeta 结构如下

CREATE TABLE `postmeta` (
  `meta_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `meta_key` text NOT NULL,
  `meta_value` text NOT NULL,
  PRIMARY KEY (`meta_id`)
)
INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(1, 1, 'count_views_all', '666'),
(2, 1, 'count_views_day-20130117', '44'),
(3, 1, 'count_views_week-201301', '343'),
(4, 2, 'count_views_all', '3453'),
(5, 3, 'count_views_all', '34553'),
(6, 4, 'count_views_all', '553'),
(7, 3, 'count_views_day-20130117', ''),
(8, 4, 'count_views_day-20130117', '35'),
(9, 5, 'count_views_day-20130111', '5'),
(10, 5, 'count_views_day-20130112', '12'),
(11, 5, 'count_views_day-20130113', '34'),
(12, 5, 'count_views_day-20130114', '45'),
(13, 5, 'count_views_day-20130115', '21'),
(14, 5, 'count_views_day-20130116', '23'),
(15, 5, 'count_views_day-20130117', '65');

想获取所有文章，并且按全部阅读量排序，理所当然，我写出了这样的查询语句：

SELECT `post`.*,`views_count` FROM `post`
JOIN
SELECT `meta_value` AS `views_count` WHERE `meta_key`='count_views_all' FROM `postmeta`
ON `post`.`id`=`postmeta`.`post_id`
ORDER BY `views_count` DESC

运行，提示 SQL 语法错误。查看 MySQL 官方文档，总结出 JOIN 两边必须是表（有错还希望看官您指出），因此，我加了一个子句。如下，OK。

SELECT `post`.*,`views_count` FROM `post`
JOIN
(SELECT `post_id`,`meta_value` AS `views_count` FROM `postmeta` WHERE `meta_key`='count_views_all') AS `meta`
ON `post`.`id`=`meta`.`post_id`
ORDER BY (`views_count`+0) DESC