表结构和数据

create table cat(      id(1) int not null auto_increment primary key,      cat_id int(1),      value int(1),      name varchar(20)  );  insert into cat (cat_id,name,value) values ('1','name1', '1');  insert into cat (cat_id,name,value) values ('1','name2', '2');  insert into cat (cat_id,name,value) values ('1','name3', '3');  insert into cat (cat_id,name,value) values ('1','name4', '4');  insert into cat (cat_id,name,value) values ('2','name5', '5');  insert into cat (cat_id,name,value) values ('2','name6', '6');  insert into cat (cat_id,name,value) values ('2','name7', '7');   insert into cat (cat_id,name,value) values ('2','name8', '8');  insert into cat (cat_id,name,value) values ('3','name9', '9');  insert into cat (cat_id,name,value) values ('3','name10','10');  insert into cat (cat_id,name,value) values ('3','name11','11');  insert into cat (cat_id,name,value) values ('3','name12','12'); mysql> select *from cat;+----+--------+-------+--------+| id | cat_id | value | name   |+----+--------+-------+--------+|  1 |      1 |     1 | name1  ||  2 |      1 |     2 | name2  ||  3 |      1 |     3 | name3  ||  4 |      1 |     4 | name4  ||  5 |      2 |     5 | name5  ||  6 |      2 |     6 | name6  ||  7 |      2 |     7 | name7  ||  8 |      2 |     8 | name8  ||  9 |      3 |     9 | name9  || 10 |      3 |    10 | name10 || 11 |      3 |    11 | name11 || 12 |      3 |    12 | name12 |+----+--------+-------+--------+12 rows in set (0.13 sec)

查询分组最大记录

// 默认取分组第一条mysql> select *from cat group by cat_id order by cat_id;+----+--------+-------+-------+| id | cat_id | value | name  |+----+--------+-------+-------+|  1 |      1 |     1 | name1 ||  5 |      2 |     5 | name5 ||  9 |      3 |     9 | name9 |+----+--------+-------+-------+3 rows in set (0.00 sec)mysql> select *from (select *from cat order by value desc) a group by cat_id;+----+--------+-------+--------+| id | cat_id | value | name   |+----+--------+-------+--------+|  4 |      1 |     4 | name4  ||  8 |      2 |     8 | name8  || 12 |      3 |    12 | name12 |+----+--------+-------+--------+3 rows in set (0.06 sec)mysql> select a.* from cat a where value = (select max(value) from cat where cat_id = a.cat_id) order by a.cat_id;mysql> select a.* from cat a,(select cat_id,max(value) value from cat group by cat_id) b where a.cat_id = b.cat_id and a.value = b.value order by a.cat_id; mysql> select a.* from cat a inner join (select cat_id, max(value) value from cat group by cat_id) b on a.cat_id= b.cat_id and a.value= b.value order by a.cat_id;+----+--------+-------+--------+| id | cat_id | value | name   |+----+--------+-------+--------+|  4 |      1 |     4 | name4  ||  8 |      2 |     8 | name8  || 12 |      3 |    12 | name12 |+----+--------+-------+--------+3 rows in set (0.00 sec)

分组前 3 条记录

mysql> select a.* from cat a where exists (select count(*) from cat where cat_id= a.cat_id and value > a.value having Count(*) < 3) order by a.cat_id,a.value desc;mysql> select *from cat a where (select count(*) from cat b where a.cat_id=b.cat_id and b.value>a.value) < 3 order by a.cat_id,a.value desc;+----+--------+-------+--------+| id | cat_id | value | name   |+----+--------+-------+--------+|  4 |      1 |     4 | name4  ||  3 |      1 |     3 | name3  ||  2 |      1 |     2 | name2  ||  8 |      2 |     8 | name8  ||  7 |      2 |     7 | name7  ||  6 |      2 |     6 | name6  || 12 |      3 |    12 | name12 || 11 |      3 |    11 | name11 || 10 |      3 |    10 | name10 |+----+--------+-------+--------+9 rows in set (0.15 sec)