-- 選取 TABLE:EBANK_MAIL 裡所有 SUBJ 為 FIN 的 E-Mail address
-- 五個 E-Mails 為一組寄送報表，以下為例:(將 MAILLIST 以五個為一組 pipe起來)
CREATE TABLE EBANK_MAIL
(
SUBJ VARCHAR(5),
MAILLIST VARCHAR(64)
) IN USERSPACE1;
INSERT INTO EBANK_MAIL VALUES ('FIN','AndyC@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','Chan@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','CLY@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','CPY@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','CFC@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','CLH@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chengs@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','Chenjl@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chenlk@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','Chenmj@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chihc@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chousc@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chuangsy@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chucw@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','DAYCC@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','FUMS@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','GEORGE@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','HOKT@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','Hotc@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','HOWH@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','KKR@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','LDC@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','MEI@ebank.com');
--使用Recursive Sql與Row_number()
WITH T(COL1,CNT,X,ML) AS
(
SELECT 0 COL1,COUNT(*) CNT,X,CAST('' AS VARCHAR(1000)) ML
FROM (SELECT MAILLIST,ROW_NUMBER() OVER() / 5 X
FROM EBANK_MAIL
WHERE SUBJ = 'FIN') TMP
GROUP BY X
UNION ALL
SELECT T.COL1 + 1,T.CNT,PLUS.X,ML|| ',' || RTRIM(PLUS.MAILLIST)
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY X) COL1,MAILLIST,X
FROM (SELECT MAILLIST,ROW_NUMBER() OVER() / 5 X
FROM EBANK_MAIL
WHERE SUBJ = 'FIN') TMP1
) PLUS,T
WHERE PLUS.X = T.X
AND PLUS.COL1 <= T.CNT
AND T.COL1 + 1 = PLUS.COL1
)
SELECT SUBSTR(ML,2) ML
FROM T
WHERE COL1 = CNT;
--結果如下
ML
----------------------------------------------------------------------------------
AndyC@ebank.com,Chan@ebank.com,CLY@EBANK.COM,CPY@ebank.com
HOWH@ebank.com,KKR@EBANK.COM,LDC@ebank.com,MEI@ebank.com
CFC@ebank.com,CLH@ebank.com,chengs@ebank.com,Chenjl@ebank.com,chenlk@ebank.com
Chenmj@ebank.com,chihc@ebank.com,chousc@ebank.com,chuangsy@ebank.com,chucw@ebank.com
DAYCC@EBANK.COM,FUMS@EBANK.COM,GEORGE@EBANK.COM,HOKT@EBANK.COM,Hotc@ebank.com
