Mysql取分组后前N个值

来源：互联网发布：贵金属喊单软件编辑：程序博客网时间：2024/05/21 06:29

表结构如下：

create table `test_group_limit` (`row_id` varchar (33),`id` int (11),`num` bigint (11),`log` varchar (33)); insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('1','1','32','e');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('2','1','21','r');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('3','1','54','f');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('4','1','31','d');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('5','1','54','g');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('6','2','32','v');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('7','2','12','b');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('8','2','21','x');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('9','2','76','n');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('10','2','45','z');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('11','2','89','a');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('12','3','32','m');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('13','3','12','k');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('14','3','76','p');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('15','3','45','i');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('16','3','90','l');insert into `test_group_limit` (`row_id`, `id`, `num`, `log`) values('17','3','65','q');

取按id分组后每组的最大值：

SELECT t1.`id`,t1.`num` FROM `test_group_limit` t1WHERE NOT EXISTS(SELECT 1 FROM  test_group_limit WHERE t1.`id`=id AND num>t1.`num`);

上面表示，通过id自关联，每组中，没有其他数能大于它的就是最大数，不能大于等于，因为两组肯定至少存在等于的情况，就查不出来数据。

取按id分组后每组的前三的值
这种方法存在问题，当存在相同值时，前三取出来的数据不一定就是只有三行

SELECT t1.`id`,t1.`num` FROM `test_group_limit` t1WHERE 3>(SELECT COUNT(1) FROM  test_group_limit WHERE t1.`id`=id AND num>t1.`num`)order by t1.id asc,t1.num desc

阅读全文

0 0