php - 如何查询前10条出现次数最多的记录

Question

我很苦恼如何查询前10个出现次数最多的升序。我想显示 tour_id 的总行数/计数。

例子:
- -
旅游编号 |总数
94 -> 2
95 -> 1
96 -> 3
97 -> 1 等

CREATE TABLE IF NOT EXISTS `tourism_most_viewedtbl` (
  `tour_most_id` int(12) NOT NULL AUTO_INCREMENT,
  `tour_token` varchar(50) NOT NULL,
  `tour_id` int(12) NOT NULL,
  `ip_address` varchar(50) NOT NULL,
  `tour_date_time` datetime NOT NULL,
   PRIMARY KEY (`tour_most_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;


INSERT INTO `tourism_most_viewedtbl` 
(`tour_most_id`, `tour_token`, `tour_id`,`ip_address`, `tour_date_time`) 
VALUES
(12, '8062ac76d8c9e9917e1ee1c6430e0c07', 96, '::1', '2014-06-11 06:59:05'),
(13, '9922c520efa6cfb682a18512166132dd', 96, '::1', '2014-06-11 06:59:54'),
(14, '8062ac76d8c9e9917e1ee1c6430e0c07', 97, '::1', '2014-06-11 07:07:03'),
(15, '8062ac76d8c9e9917e1ee1c6430e0c07', 94, '::1', '2014-06-11 07:30:35'),
(16, '8062ac76d8c9e9917e1ee1c6430e0c07', 95, '::1', '2014-06-11 07:51:15'),
(19, '8062ac76d8c9e9917e1ee1c6430e0c07', 98, '::1', '2014-06-11 09:07:32'),
(24, '8062ac76d8c9e9917e1ee1c6430e0c07', 99, '::1', '2014-06-11 10:00:34'),
(25, '973cc524bbde8da4d88cd5f7e47fb513', 96, '::1', '2014-06-11 02:31:03'),
(26, '973cc524bbde8da4d88cd5f7e47fb513', 94, '::1', '2014-06-11 02:31:29');

Answer 1

select tour_id, count(*) as total_count
from tourism_most_viewedtbl
group by tour_id
order by total_count desc
limit 10