好像ORACLE中有相应的函数，可惜在MSSQL 或者MySQL中没有对应的函数。后两者就得用临时表来实现了。
create table company
(dep char(10) not null,
val1 int unsigned not null
);
insert into company values
('市场部', 26),
('市场部',25),
('市场部',24),
('办公室',16),
('办公室',12),
('研发部',19),
('研发部',11);
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 ;
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)
本文转自 yarin 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/85145，如需转载请自行联系原作者
