好像ORACLE中有相应的函数，可惜在MSSQL 或者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`()
BEGIN
declare 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();
query result(7 records)
| dep |
val1 |
num |
| 办公室 |
16 |
1 |
| 办公室 |
12 |
2 |
| 市场部 |
26 |
1 |
| 市场部 |
25 |
2 |
| 市场部 |
24 |
3 |
| 研发部 |
19 |
1 |
| 研发部 |
11 |
2 |
uery result(7 records)
| dep |
val1 |
num |
| 办公室 |
16 |
1 |
| 办公室 |
12 |
2 |
| 市场部 |
26 |
1 |
| 市场部 |
25 |
2 |
| 市场部 |
24 |
3 |
| 研发部 |
19 |
1 |
| 研发部 |
11 |
2 |
(7 row(s)returned)
(15 ms taken)
(0 row(s)affected)
(0 ms taken)
(7 row(s)returned)
(16 ms taken)
(0 row(s)affected)
(0 ms taken)
