ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一个复杂问题的求解过程(二)

一个复杂问题的求解过程(二)

原创 Linux操作系统 作者:yangtingkun 时间:2008-03-07 16:18:15 0 删除 编辑

今天在PUB里面看到一个帖子:http://www.itpub.net/thread-949571-1-1.html。问题本身并不复杂,不过想借这个问题简单描述一下求解的思路。

上一篇文章中已经通过硬编码方式的SQL实现,下面通过函数来构造通用解。

一个复杂问题的求解过程(一):http://yangtingkun.itpub.net/post/468/456641

 

 

之所以说上一篇文章中给出的SQL是硬编码方式,是由于在写SQL的时候参考了T表中记录条数。

由于构造子查询个数需要和表中的记录数相等,因此这里写SQL的时候构造了5个子查询的连接。而如果表中的记录发生变化,则这个SQL就不正确了,因此这种没有通用性的SQL是不符合要求的。

但是对于SQL来说,很难动态确定表连接的个数。这里最简单的方法就是通过一个PL/SQL来实现。通过PL/SQL根据T表中的记录情况,来动态构造这个查询的SQL语句。

SQL> WITH T1 AS
  2  (
  3  SELECT ID, VALUE,
  4   (
  5    SELECT MAX(SYS_CONNECT_BY_PATH(VALUE * (LEVEL - 1), ','))
  6    FROM DUAL CONNECT BY ROWNUM <= POWER
  7   ) POWER
  8  FROM T
  9  )
 10  SELECT L1, L2, L3, L4, L5
 11  FROM
 12  (
 13   SELECT A.LV L1, B.LV L2, C.LV L3, D.LV L4, E.LV L5,
 14    A.VALUE + B.VALUE + C.VALUE + D.VALUE + E.VALUE TOTAL
 15   FROM
 16   (
 17    SELECT LEVEL - 1 LV,
 18     SUBSTR(POWER,
 19      INSTR(POWER, ',', 1, ROWNUM) + 1,
 20      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
 21    FROM (SELECT * FROM T1 WHERE ID = 1)
 22    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
 23   ) A,
 24   (
 25    SELECT LEVEL - 1 LV,
 26     SUBSTR(POWER,
 27      INSTR(POWER, ',', 1, ROWNUM) + 1,
 28      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
 29    FROM (SELECT * FROM T1 WHERE ID = 2)
 30    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
 31   ) B,
 32   (
 33    SELECT LEVEL - 1 LV,
 34     SUBSTR(POWER,
 35      INSTR(POWER, ',', 1, ROWNUM) + 1,
 36      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
 37    FROM (SELECT * FROM T1 WHERE ID = 3)
 38    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
 39   ) C,
 40   (
 41    SELECT LEVEL - 1 LV,
 42     SUBSTR(POWER,
 43      INSTR(POWER, ',', 1, ROWNUM) + 1,
 44      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
 45    FROM (SELECT * FROM T1 WHERE ID = 4)
 46    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
 47   ) D,
 48   (
 49    SELECT LEVEL - 1 LV,
 50     SUBSTR(POWER,
 51      INSTR(POWER, ',', 1, ROWNUM) + 1,
 52      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
 53    FROM (SELECT * FROM T1 WHERE ID = 5)
 54    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
 55   ) E
 56  )
 57  WHERE TOTAL = 16;

        L1         L2         L3         L4         L5
---------- ---------- ---------- ---------- ----------
         0          0          2          0          2
         0          0          3          0          1
         1          1          0          1          2
         1          1          1          1          1
         1          1          2          1          0
         2          0          1          0          2
         2          0          2          0          1
         2          0          3          0          0

已选择8行。

有了这个SQL的原型,构造动态SQL就很简单了。问题的关键是确定哪些部分是动态的,那些部分是不变的。

上面的SQL中子查询的名字ABCDE,对于构造动态SQL比较麻烦,这里替换为A1A2A3A4A5

SQL> CREATE OR REPLACE FUNCTION F_RESULT RETURN SYS_REFCURSOR AS
  2   V_STR VARCHAR2(32767);
  3   V_COUNT NUMBER;
  4   C_CURSOR SYS_REFCURSOR;
  5  BEGIN
  6   SELECT COUNT(*) INTO V_COUNT FROM T;
  7   V_STR := 'WITH T1 AS
  8  (
  9  SELECT ROWNUM, ID, VALUE,
 10   (
 11    SELECT MAX(SYS_CONNECT_BY_PATH(VALUE * (LEVEL - 1), '',''))
 12    FROM DUAL CONNECT BY ROWNUM <= POWER
 13   ) POWER
 14  FROM T
 15  )
 16  SELECT ';
 17   FOR I IN 1..V_COUNT LOOP
 18    V_STR := V_STR || 'L' || I || ', ';
 19   END LOOP;
 20   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
 21  FROM
 22  (
 23   SELECT ';
 24   FOR I IN 1..V_COUNT LOOP
 25    V_STR := V_STR || 'A' || I || '.LV L' || I || ', ';
 26   END LOOP;
 27   FOR I IN 1..V_COUNT LOOP
 28    V_STR := V_STR || 'A' || I || '.VALUE + ';
 29   END LOOP;
 30   V_STR := RTRIM(V_STR, '+ ') || ' TOTAL
 31  FROM
 32  ';
 33   FOR I IN 1..V_COUNT LOOP
 34    V_STR := V_STR || '(SELECT LEVEL - 1 LV,
 35   SUBSTR(POWER,
 36    INSTR(POWER, '','', 1, ROWNUM) + 1,
 37    INSTR(POWER || '','', '','', 1, ROWNUM + 1) - INSTR(POWER, '','', 1, ROWNUM) - 1) VALUE
 38  FROM (SELECT * FROM T1 WHERE ID = ' || I || ')
 39  CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, '',''))) A' || I || ',
 40  ';
 41   END LOOP;
 42   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
 43  )
 44  WHERE TOTAL = 16';
 45   OPEN C_CURSOR FOR V_STR;
 46   RETURN C_CURSOR;
 47  END;
 48  /

函数已创建。

过程没有太多值得描述的,为了偷懒,SQL结果输出的麻烦,这里使用REF CURSOR作为范围结果,将游标直接输出:

SQL> SELECT F_RESULT FROM DUAL;

F_RESULT
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        L1         L2         L3         L4         L5
---------- ---------- ---------- ---------- ----------
         0          0          2          0          2
         0          0          3          0          1
         1          1          0          1          2
         1          1          1          1          1
         1          1          2          1          0
         2          0          1          0          2
         2          0          2          0          1
         2          0          3          0          0

已选择8行。

另外,如果表中的记录数据量不大的话,可以通过下面的方法来简化SQL的写法:

SQL> WITH T1 AS
  2  (
  3  SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWER
  4  FROM T
  5  CONNECT BY LEVEL <= T.POWER
  6  )
  7  SELECT L1, L2, L3, L4, L5
  8  FROM
  9  (
 10   SELECT A1.LV L1, A2.LV L2, A3.LV L3, A4.LV L4, A5.LV L5,
 11    A1.POWER + A2.POWER + A3.POWER + A4.POWER + A5.POWER TOTAL
 12   FROM
 13   (
 14    SELECT *
 15    FROM T1
 16    WHERE ID = 1
 17   )A1,
 18   (
 19    SELECT *
 20    FROM T1
 21    WHERE ID = 2
 22   )A2,
 23   (
 24    SELECT *
 25    FROM T1
 26    WHERE ID = 3
 27   )A3,
 28   (
 29    SELECT *
 30    FROM T1
 31    WHERE ID = 4
 32   )A4,
 33   (
 34    SELECT *
 35    FROM T1
 36    WHERE ID = 5
 37   )A5
 38  )
 39  WHERE TOTAL = 16
 40  ;

        L1         L2         L3         L4         L5
---------- ---------- ---------- ---------- ----------
         2          0          3          0          0
         1          1          2          1          0
         2          0          2          0          1
         0          0          3          0          1
         1          1          1          1          1
         2          0          1          0          2
         0          0          2          0          2
         1          1          0          1          2

已选择8行。

注意,这里说的是简化而不是优化。WITH语句中如果不加DISTINCT会产生重复的数据,尤其是数据量大的情况。不过数据量小的情况,这种方法的效率并不低。

对应的函数方法:

SQL> CREATE OR REPLACE FUNCTION F_RESULT RETURN SYS_REFCURSOR AS
  2   V_STR VARCHAR2(32767);
  3   V_COUNT NUMBER;
  4   C_CURSOR SYS_REFCURSOR;
  5  BEGIN
  6   SELECT COUNT(*) INTO V_COUNT FROM T;
  7   V_STR := 'WITH T1 AS
  8  (
  9  SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWER
 10  FROM T
 11  CONNECT BY LEVEL <= T.POWER
 12  )
 13  SELECT ';
 14   FOR I IN 1..V_COUNT LOOP
 15    V_STR := V_STR || 'L' || I || ', ';
 16   END LOOP;
 17   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
 18  FROM
 19  (
 20   SELECT ';
 21   FOR I IN 1..V_COUNT LOOP
 22    V_STR := V_STR || 'A' || I || '.LV L' || I || ', ';
 23   END LOOP;
 24   FOR I IN 1..V_COUNT LOOP
 25    V_STR := V_STR || 'A' || I || '.POWER + ';
 26   END LOOP;
 27   V_STR := RTRIM(V_STR, '+ ') || ' TOTAL
 28  FROM
 29  ';
 30   FOR I IN 1..V_COUNT LOOP
 31    V_STR := V_STR || ' (
 32    SELECT *
 33    FROM T1
 34    WHERE ID = ' || I || '
 35   )A' || I || ',
 36  ';
 37   END LOOP;
 38   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
 39  )
 40  WHERE TOTAL = 16';
 41   OPEN C_CURSOR FOR V_STR;
 42   RETURN C_CURSOR;
 43  END;
 44  /

函数已创建。

SQL> SELECT F_RESULT FROM DUAL;

F_RESULT
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        L1         L2         L3         L4         L5
---------- ---------- ---------- ---------- ----------
         2          0          3          0          0
         1          1          2          1          0
         2          0          2          0          1
         0          0          3          0          1
         1          1          1          1          1
         2          0          1          0          2
         0          0          2          0          2
         1          1          0          1          2

已选择8行。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-201573/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10389566