获取分组的前几组方法

最新推荐文章于 2022-04-22 17:37:37 发布

weixin_34411563

最新推荐文章于 2022-04-22 17:37:37 发布

阅读量295

点赞数

文章标签： java javascript python ViewUI

原文链接：https://my.oschina.net/u/585111/blog/219491

版权

2019独角兽企业重金招聘Python工程师标准>>>

获取分组的前几组方法。

/*Table structure for table `t` */

DROP TABLE IF EXISTS `t`;

CREATE TABLE `t` (
  `id` int(11) NOT NULL auto_increment,
  `g_id` int(11) NOT NULL,
  `t_str` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  key (`g_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

/*Data for the table `t` */

insert into `t`(`id`,`g_id`,`t_str`) values
(1,2,'wo'),
(2,2,'ni'),
(3,2,'ta'),
(4,3,'wo '),
(5,4,'ni'),
(6,3,'ni'),
(7,4,'ta'),
(8,3,'wang'),
(9,4,'li'),
(10,3,'hai'),
(11,4,'ri'),
(12,2,'ren'),
(13,5,'ta'),
(14,6,'ri'),
(15,6,'ren'),
(16,6,'fuck'),
(17,6,'shit'),
(18,5,'ls'),
(19,5,'chmod'),
(20,5,'chgrp'),
(21,5,'chown'),
(22,3,'rm'),
(23,3,'desc'),
(24,4,'pwd'),
(25,5,'cd');
1、相关子查询
（这个SQL语句是从ITPUB上来的。）
select a.* from t a where
(
select count(*) from t b where a.g_id = b.g_id and b.id<a.id
) < 2 order by a.g_id desc;

2、存储过程。
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_get_num_group`$$

CREATE PROCEDURE `test`.`sp_get_num_group`(
 IN f_num int)
BEGIN
  -- The variable stands for totla number of the record.
  declare cnt int default 0;
  declare i int;
  -- Create temp table to reserved the result.
  create temporary table if not exists tmp select * from t where 1 = 0;
  -- Get the total number of the group by record.
  select count(*) from
  (
    select count(*) from t group by g_id order by null
  ) T into cnt;
  set i = 0;
  while i < cnt
  do
    -- Get the real g_id one by one.
    set @stmt = concat('select g_id from t group by g_id order by g_id desc limit ',i,',1 into @tmp_id');
    prepare s1 from @stmt;
    execute s1;
    drop prepare s1;
    set @stmt = '';
    -- Get the needed data.
    set @stmt = concat('insert into tmp select * from t where g_id = ',@tmp_id,' limit ',f_num);
    prepare s1 from @stmt;
    execute s1;
    drop prepare s1;
    set @stmt = '';
    set @tmp_id = 0;
    set i = i + 1;
  end while;
  -- Get the record from temp table.
  select * from tmp order by g_id desc,id desc;
  -- Drop temp table.
  drop table tmp;
END$$

DELIMITER ;