- 論壇徽章:
- 0
|
在一個(gè)論壇上有人發(fā)個(gè)帖子問這個(gè)問題：
有一表的記錄為：
Task_ID Employee_Name STEPWORKTIME
6262 張三 2
6262 李四 2
6262 王二 1.5
6265 成某 2
6265 趙某 2
6265 錢某 1.5
……
我想求一個(gè)函數(shù)能將Task_ID字段相同記錄合并為一條記錄。
即想得到如下結(jié)果：
Task_ID Employees WorkLoad
6262 張三、李四、王二 5.5
6265 成某、趙某、錢某 5.5
……
我寫了一下，沒寫出UDF，只寫了個(gè)存儲(chǔ)過程。能應(yīng)付他大概的要求。
可是樓主不怎么滿意，想著與其丟掉，不如索性貼出來，大家批評(píng)批評(píng)^_^。
先創(chuàng)建一個(gè)表，名為FromTable
db2 create table FromTable(id varchar(10),name varchar(200),stepworktime int)
插入數(shù)據(jù)
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)
現(xiàn)在再創(chuàng)建一個(gè)表，為ToTable
跟test1000一樣的結(jié)構(gòu)，用一個(gè)存儲(chǔ)過程把你要的結(jié)果插進(jìn)去
創(chuàng)表
db2 create TABLE ToTable ( id varchar(100), name varchar(100),sum int )
寫存儲(chǔ)過程
CREATE PROCEDURE ADMINISTRATOR.ProcConcatName ( )
------------------------------------------------------------------------
--SQL 存儲(chǔ)過程
--Sisijian
--2005-01-20
------------------------------------------------------------------------
Lable1: begin
------------------------------------------------------------------------
--定義變量
--v_NumOfRecd存放對(duì)應(yīng)FromTable每個(gè)id記錄條數(shù)
--v_Index控制當(dāng)前記錄是在id相同的記錄中第幾條
--v_id等三個(gè)變量用于存放臨時(shí)數(shù)據(jù)
--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;
------------------------------------------------------------------------
--游標(biāo)移動(dòng)一次，就到一個(gè)新的id，id不會(huì)重復(fù)，因?yàn)榻?jīng)過上面的group by
------------------------------------------------------------------------
OPEN C1;
Concat_Loop:
LOOP
FETCH C1 INTO v_Id, v_NumOfRecd;
IF at_end = 1 THEN
LEAVE Concat_Loop;
END IF;
------------------------------------------------------------------------
--遇到每個(gè)id ，第一條記錄都應(yīng)該直接插入的
------------------------------------------------------------------------
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數(shù)據(jù)
------------------------------------------------------------------------
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
我的數(shù)據(jù)類型設(shè)錯(cuò)了，所以運(yùn)行改存儲(chǔ)過程后，查詢ToTable表結(jié)果會(huì)有點(diǎn)點(diǎn)出入。
D:\>;db2 select * from totable
ID
NAME
SUM
--------------------------------------------------------------------------------
-------------------- -----------------------------------------------------------
----------------------------------------- -----------
6262
張三,李四,王二
5
6265
趙某,錢某,成某
5
2 條記錄已選擇。
寫得不好，請(qǐng)大家多體諒下呵：） |
|
免費(fèi)注冊(cè)
發(fā)表于 2005-01-21 21:25
