关于分组序号在MySQL中的实现

chaosxin

1、表结构以及示例数据。create table company　　(dep char(10) not null,
　　val1 int unsigned not null
　　);
　　insert into company values
　　(
'市场部', 26),　　('市场部',25),
　　('市场部',24),
　　('办公室',16),
　　('办公室',12),
　　('研发部',19),
　　('研发部'
,11);2、存储过程1）、循环实现　　DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto`()BEGINdeclare cnt int default 0;declare i int default 0;drop table if exists tmp;　　-- Temporary table to save the result.
create temporary table tmp like company;alter table tmp add num int unsigned not null;select count(1) as total from (select count(1) from company where 1 group by dep) T into cnt;while i < cnt　　do
set @stmt = concat('select dep from company where 1 group by dep order by dep asc limit ',i,',1 into @t_dep');prepare s1 from @stmt;execute s1;deallocate prepare s1;set @stmt = NULL;set @num = 0;set @stmt2 = concat('insert into tmp select dep,val1,@num := @num + 1 as sequence from company where dep = ''',@t_dep,''' order by dep asc');prepare s1 from @stmt2;execute s1;deallocate prepare s1;set @stmt2 = NULL;set i = i + 1;end while;select * from tmp;set @t_dep = NULL;END$$　　DELIMITER ;
　　2）、游标实现
DELIMITER $$　　DROP PROCEDURE IF EXISTS `sp_generate_auto_cursor`$$
　　CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto_cursor`()
　　BEGIN
　　declare done1 int default 0;
　　declare a char(10);
　　declare i int unsigned default 0;
　　-- Cursor one to get the group total
　　declare cur1 cursor for select dep from company group by dep;
　　declare continue handler for 1329 set done1 = 1;
　　-- Temporary table to save the result.
　　  drop table if exists tmp;
　　create table tmp like company;
　　alter table tmp add num int unsigned not null;
　　open cur1;
　　while done1 != 1
　　do
　　fetch cur1 into a;
　　if not done1 then
　　set @i = 0;
　　begin
　　declare done2 int default 0;
　　declare b int unsigned default 0;
　　declare c int unsigned default 0;
　　-- Cursor two to get per group total.
　　declare cur2 cursor for select val1,@i := @i + 1 from company where dep = a;
　　declare continue handler for 1329 set done2 = 1;
　　open cur2;
　　while done2  1
　　do
　　fetch cur2 into b,c;
　　if not done2 then
　　insert into tmp select a,b,c;
　　end if;
　　end while;
　　close cur2;
　　end;
　　end if;
　　end while;
　　close cur1;
　　select * from tmp;
　　END$$
　　DELIMITER ;
　　3、调用结果
　　call sp_generate_auto();
　　call sp_generate_auto_cursor();