關于分組序号在MySQL中的實作

好像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
	25
	24	3
研發部	19
	11

uery result(7 records)

(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)