类似的问题,其实经常可以在各BBS上看到有人提起,但从来没有人认为可以用一条SQL实现,一般回复者给出的解决方案都是用存储过程。不过既然自己搞DB2已近11个月,水平也应该逐步地提高了,不能始终停留在前人说不可能实现,自己就认为也不能实现的程度,有必要自己独立思索一下。
这个问题的难点在于:需要分组字段对应的所有值的最大个数(行数)不明确。如果明确知道这个最大值的话是可以用CASE WHEN语句来实现的;在不知道这个最大值的情况下,首先让人想到的就是必须用循环来实现,但FOR,LOOP,WHILE,REPEAT这四种循环,在DB2中只有存储过程支持,这就不难理解为什么一般的解决方案都是说要用存储过程了。
以前认真读过SQLLIB/samples下的很多例子,所以很自然地想到一个思路——递归。试了试,成功了,无论XM字段每种值对应多少行,都一样可用。实验过程如下:
DROP TABLE TEST;
CREATE TABLE TEST (XM VARCHAR(8), SL INTEGER);
INSERT INTO TEST
VALUES ('王一', 2),
('王一', 5),
('张二', 4),
('张二', 5),
('张二', 8),
('李三', 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 =>
最后我还是认为:这样的问题,用SQL来实现实在是自找麻烦,文字处理本来就是SHELL SCRIPT的强项,所以用awk会感觉异常简单。
11:31:10 ewdbkjy:[/home/ewadmin]$cat 9
王一 2
王一 5
张二 4
张二 5
张二 8
李三 2
李三 4
李三 15
李三 29
11:31:25 ewdbkjy:[/home/ewadmin]$awk '{a[$1]=a[$1]" "$1" "$2} END{for(i in a) print a[i]}' 9
李三 2 李三 4 李三 15 李三 29
张二 4 张二 5 张二 8
王一 2 王一 5
11:31:45 ewdbkjy:[/home/ewadmin]$
| |
我的实践例子:
table: CREATE TABLE TEST (XM VARCHAR(8), SL INTEGER); INSERT INTO TEST VALUES ('王一', 2), ('王一', 5), ('张二', 4), ('张二', 5), ('张二', 8), ('李三', 2), ('李三', 4), ('李三', 15), ('李三', 29);
case 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;
result: 李三 2 李三 4 李三 15 李三 29 王一 2 王一 5 张二 4 张二 5 张二 8
case 2:
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, rtrim(ltrim(CHAR(A.SL))) FROM TEST A UNION ALL SELECT C.FATHER,C.SON,C.XM,rtrim(ltrim(B.CHAIN))||','||rtrim(ltrim(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, rtrim(ltrim(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;
result: XM CHAIN -------- ----------- 李三 2,4,15,29 王一 2,5 张二 4,5,8
case 3:
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, rtrim(ltrim(CHAR(A.SL))) FROM TEST A UNION ALL SELECT C.FATHER,C.SON,C.XM,rtrim(ltrim(B.CHAIN))||rtrim(ltrim(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, rtrim(ltrim(CHAR(A.SL))) AS SL FROM TEST A) AS C, B WHERE B.SON= C.FATHER) -- end with
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;
result: XM CHAIN -------- ----------- 李三 241529 王一 25 张二 458
|