获取分组的前几组方法。
/*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 ;
本文转自 david_yeung 51CTO博客，原文链接：
http://blog.51cto.com/yueliangdao0608/81270
，如需转载请自行联系原作者
