关于多次UNION ALL问题的行列转换！

原帖请见： http://www.itpub.net/forum.phpmod=viewthread&tid=1627350&page=1

 

建表语句及测试数据语句如下：

 

create table tbl(id int,x1 varchar2(2),x2 varchar2(2));
INSERT INTO tbl VALUES (1, NULL, 'a');
INSERT INTO tbl VALUES (2, 'b', NULL);
INSERT INTO tbl VALUES (3, 'c', 'd');

 

现在要把tbl表中列x1，x2转换为行，即对于一条记录（id，x1,x2），如果x1不为null，则转化为一行（id，x1）；若x2也不为null，则也转为一行（id，x2）

 

要输出的结果如下：

 

--输出：
ID X
1 a
2 b
3 c
3 d

我的做法：

SQL> WITH t AS
  2    (SELECT id,x1 FROM tbl
  3    UNION ALL
  4    SELECT id,x2 x1 FROM tbl
  5    )
  6  SELECT t.id,t.x1 FROM t WHERE t.x1 IS NOT NULL ORDER BY id
  7  /

        ID X1
---------- --
         1 a
         2 b
         3 d
         3 c

已用时间:  00: 00: 00.01
SQL>

 

其实这个问题比较简单，只要union all一下再做过滤即可。

但这种情况下要做两次扫描。那有没有什么办法只做一次全表扫描呢？

请看以下的答案：

SQL> SELECT id,
  2    DECODE(b.rn, 1, t.x1, t.x2)
  3  FROM tbl t,
  4    (SELECT rownum rn FROM dual CONNECT BY rownum<=2
  5    ) b
  6  WHERE DECODE(b.rn, 1, t.x1, t.x2) IS NOT NULL
  7  /

        ID DE
---------- --
         2 b
         3 c
         1 a
         3 d

已用时间:  00: 00: 00.01
SQL>

点评：这种方法利用dual表巧妙地构造了一个连接表，这样一来便实现了只扫描一次的功能！妙！！

 

 

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

转载于:http://blog.itpub.net/26977915/viewspace-733826/