- 论坛徽章:
- 1
|
节选自我的BLOG
http://blog.chinaunix.net/u/739/showart.php?id=278401
DROP TABLE TEST;
CREATE TABLE TEST (XM VARCHAR(, SL INTEGER);
INSERT INTO TEST
VALUES ('王一', 2),
('王一', 5),
('张二', 4),
('张二', 5),
('张二', ,
('李三', 2),
('李三', 4),
('李三', 15),
('李三', 29);
WITH B (FATHER,SON,XM,CHAIN) AS
(SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CAST(CHAR(A.SL) AS VARCHAR(100))
FROM TEST A
UNION ALL
SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || ' ' || C.SL
FROM (SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B
WHERE B.SON= C.FATHER)
SELECT
D.XM || ' ' || D.CHAIN
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
WHERE
D.ROW_NUM = 1;
效果如下:
db2 => WITH B (FATHER,SON,XM,CHAIN) AS
db2 (cont.) => (SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CAST(CHAR(A.SL) AS VARCHAR(100))
db2 (cont.) => FROM TEST A
db2 (cont.) => UNION ALL
db2 (cont.) => SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || ' ' || C.SL
db2 (cont.) => FROM (SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B
db2 (cont.) => WHERE B.SON= C.FATHER)
db2 (cont.) => SELECT
db2 (cont.) => D.XM || ' ' || D.CHAIN
db2 (cont.) => FROM
db2 (cont.) => (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
db2 (cont.) => WHERE
db2 (cont.) => D.ROW_NUM = 1;
WITH B (FATHER,SON,XM,CHAIN) AS (SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CAST(CHAR(A.SL) AS VARCHAR(100)) FROM TEST A UNION ALL SELECT C.FATHER,C.SON,C.XM,B.CHAIN || C.XM || ' ' || C.SL FROM (SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON, A.XM, CHAR(A.SL) AS SL FROM TEST A) AS C, B WHERE B.SON= C.FATHER) SELECT D.XM || ' ' || D.CHAIN FROM (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D WHERE D.ROW_NUM = 1
1
--------------------------------------------------------------------------------------------------------------
SQL0347W 递归公共表表达式 "DB2ADMIN.B" 可能包含无限循环。 SQLSTATE=01605
李三 2 李三 4 李三 15 李三 29
王一 2 王一 5
张二 4 张二 5 张二 8
已选择 3 条记录,打印 1 条警告消息。
db2 =>
相信用递归的思路处理类似问题,在此之前没有人提到过。 |
|