一个SQL存储过程

sisijian

在一个论坛上有人发个帖子问这个问题：

有一表的记录为：
Task_ID   Employee_Name  STEPWORKTIME
   6262         张三               2
   6262         李四          2
   6262         王二                1.5
   6265         成某               2
   6265         赵某         2
   6265         钱某         1.5
……
我想求一个函数能将Task_ID字段相同记录合并为一条记录。
即想得到如下结果：
Task_ID Employees                 WorkLoad
   6262    张三、李四、王二          5.5     
   6265    成某、赵某、钱某          5.5
   ……

我写了一下，没写出UDF，只写了个存储过程。能应付他大概的要求。
可是楼主不怎么满意，想着与其丢掉，不如索性贴出来，大家批评批评^_^。

先创建一个表，名为FromTable
db2 create table FromTable(id varchar(10),name varchar(200),stepworktime int)
插入数据
db2 insert into FromTable values ('6262','张三',2)
db2 insert into FromTable values ('6262','李四',2)
db2 insert into FromTable values ('6262','王二',1.5)
db2 insert into FromTable values ('6265','成某',2)
db2 insert into FromTable values ('6265','赵某',2)
db2 insert into FromTable values ('6265','钱某',1.5)

现在再创建一个表，为ToTable
跟test1000一样的结构，用一个存储过程把你要的结果插进去
创表
db2 create  TABLE ToTable ( id  varchar(100), name varchar(100),sum int )

写存储过程


CREATE PROCEDURE ADMINISTRATOR.ProcConcatName (  )
------------------------------------------------------------------------
--SQL 存储过程
--Sisijian
--2005-01-20
------------------------------------------------------------------------

Lable1: begin

------------------------------------------------------------------------
--定义变量
--v_NumOfRecd存放对应FromTable每个id记录条数
--v_Index控制当前记录是在id相同的记录中第几条
--v_id等三个变量用于存放临时数据
--at_end控制是否到底
------------------------------------------------------------------------
   DECLARE SQLSTATE CHAR(5);
   DECLARE v_NumOfRecd int;
   DECLARE v_Index int;
   DECLARE v_Id varCHAR(100);
   DECLARE v_ConcatedName varchar(5000);
   DECLARE v_SumOfWorkTime int;
   DECLARE at_end INT DEFAULT 0;


   DECLARE not_found CONDITION FOR SQLSTATE '02000';
   DECLARE C1 CURSOR FOR
     SELECT id, count(*)
     FROM FromTable   
     GROUP BY id
     ORDER BY id;
   
   DECLARE CONTINUE HANDLER FOR not_found
     SET at_end = 1;

------------------------------------------------------------------------
--游标移动一次，就到一个新的id，id不会重复，因为经过上面的group by
------------------------------------------------------------------------
   OPEN C1;
   Concat_Loop:
   LOOP
     FETCH C1 INTO v_Id, v_NumOfRecd;
     IF at_end = 1 THEN
       LEAVE Concat_Loop;
     END IF;
------------------------------------------------------------------------
--遇到每个id ，第一条记录都应该直接插入的
------------------------------------------------------------------------
        SET v_Index=1;
        SET v_ConcatedName = (SELECT name  FROM
                (SELECT ROW_NUMBER() over()  as a ,FromTable.*  FROM FromTable where id = v_Id) as x where a=v_Index );
        SET v_SumOfWorkTime = (SELECT STEPWORKTIME  FROM
                (SELECT ROW_NUMBER() over()  as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
    INSERT INTO ToTable VALUES (v_Id, v_ConcatedName,v_SumOfWorkTime);
        SET v_Index=2;
------------------------------------------------------------------------
--如果有第二条的话，就连接名字字符串，累加STEPWORKTIME数据
------------------------------------------------------------------------
Inner_Loop:
LOOP
        IF v_Index = (v_NumOfRecd+1)  THEN
                LEAVE Inner_Loop;
        ELSE
                SET v_ConcatedName = v_ConcatedName||','||(SELECT name  FROM
                        (SELECT ROW_NUMBER() over()  as a,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
                SET v_SumOfWorkTime = v_SumOfWorkTime+(SELECT STEPWORKTIME  FROM
                        (SELECT ROW_NUMBER() over()  as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
                       
        UPDATE ToTable SET name =  v_ConcatedName where id = v_Id;
        UPDATE ToTable SET num =  v_SumOfWorkTime where id = v_Id;
        SET v_Index=v_Index+1;
        END IF ;

END LOOP Inner_Loop;
   END LOOP Concat_Loop;
   CLOSE C1;
END Lable1

我的数据类型设错了，所以运行改存储过程后，查询ToTable表结果会有点点出入。
D:\>;db2 select * from totable

ID
                     NAME
                                          SUM
--------------------------------------------------------------------------------
-------------------- -----------------------------------------------------------
----------------------------------------- -----------
6262
                     张三,李四,王二
                                                    5
6265
                     赵某,钱某,成某
                                                    5

  2 条记录已选择。

写得不好，请大家多体谅下呵：）

liuxuhui

十分感谢

daliwa

楼主不用太过谦虚，实在不错了，
偶对游标不是很熟，受教了

mymm

很好！

wolf9653

i 不错！

sunning_cho

提个问题，如果fromtab等于某个值的记录不等，你的存储过程是否还成立？呵呵



如果是按照你的思路，我的意见是在CUROSR中将人名合并起来（中间用逗号或者其他符号分隔），然后UPDATE到表字段中（此字段要考虑要有充分大），如在应用中需要逐个显示再根据分隔符分别显示出来

exam:  

    1234    a1, a2, a3,  a4    5.5

sisijian

是这样考虑的：

如果某个ID只有一条记录，根据GROUP BY ，则count(*)后也会因一条
这样也会插入ToTable一条记录
如果某个ID相同记录多于一条，则插入一条后继续UPDATE

人世间

好贴，收藏了。

20228136

厉害