MYSQL中查询TOP N的问题

MYSQL中查询TOP N的问题

测试数据

CREATE TABLE IF NOT EXISTS `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DELETE FROM `people`;
INSERT INTO `people` (`id`, `name`, `city`, `age`) VALUES
    (1, 'cs1', '长沙', 20),
    (2, 'cs2', '长沙', 25),
    (3, 'cs3', '长沙', 30),
    (4, 'cs4', '长沙', 40),
    (5, 'sh1', '上海', 22),
    (6, 'sh2', '上海', 24),
    (7, 'sh3', '上海', 26),
    (8, 'sh4', '上海', 28),
    (9, 'nj1', '南京', 14),
    (10, 'nj2', '南京', 5),
    (11, 'nj3', '南京', 40),
    (12, 'nj4', '南京', 16),
    (13, 'sz1', '深圳', 28),
    (14, 'sz2', '深圳', 35),
    (15, 'sz3', '深圳', 60),
    (16, 'sz4', '深圳', 21);

select * from people;

1.对于简单情况

1>查询某些条件下的TOP N(查询年龄最大的5个人)

select * from people where 1=1  order by  age desc limit 5;

2>查询最大值/最小值(查询年龄最大/最小的人)

select * from people where age = (select max(age) from people);
select * from people where age = (select min(age) from people);

3>查询高于平均值的数据(查询年龄高于平均值的人)

select * from people where age > (select avg(age) from people);

2.分组TOP

1>查询分组TOP 1(查询每个城市年龄最大的人)

select * from people p , (select city, max(age) maxAge from people group by city) t
where p.city=t.city and p.age=t.maxAge;

2>查询分组TOP N(查询每个城市年龄前N的人)

此种情况可拆分成2个步骤

1)查询各城市的各年龄的rownum值

SELECT t.city, t.age,
( CASE 
WHEN @c=t.city THEN @n:=@n+1 
ELSE  (
CASE @c:=t.city WHEN t.city THEN @n:=1 END
) END) grouprownum
FROM 
(
SELECT city, age, @n:=0, @c:=''
FROM people
GROUP BY city, age
ORDER BY city, age DESC) t

2)查询年龄前N(2)的人

select * from 
(SELECT t.city, t.age,
( CASE 
WHEN @c=t.city THEN @n:=@n+1 
ELSE  (
CASE @c:=t.city WHEN t.city THEN @n:=1 END
) END) grouprownum
FROM 
(
SELECT city, age, @n:=0, @c:=''
FROM people
GROUP BY city, age
ORDER BY city, age DESC) t) gt where gt.grouprownum <=2;

主要使用了MYSQL的用户自定义变量和case…when语句来处理分组排序的问题
其他更复杂的情况可以依据此种方法推导

参考博文
http://www.cnblogs.com/advocate/archive/2012/03/02/2376900.html
http://blog.csdn.net/tangtong1/article/details/50996669