Mysql取分组后前N个值

朴灿烈づ我的快乐病毒、 2022-06-08 05:12 222阅读 0赞

表结构如下：

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` t1
    WHERE 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` t1
    WHERE 3>(SELECT COUNT(1) FROM  test_group_limit WHERE t1.`id`=id AND num>t1.`num`)
    order by t1.id asc,t1.num desc