关于分组序号在MySQL中的实现
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 done21
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();
页:
[1]