摘要:表结构和数据查询分组最大记录默认取分组第一条分组前条记录
表结构和数据
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)
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/25550.html
阅读 3471·2021-09-22 15:50
阅读 3216·2019-08-30 15:54
阅读 2693·2019-08-30 14:12
阅读 3035·2019-08-30 11:22
阅读 2055·2019-08-29 11:16
阅读 3551·2019-08-26 13:43
阅读 1157·2019-08-23 18:33
阅读 897·2019-08-23 18:32