请教一个高难度的SQL文

Question

create table T
(col1 varchar2(20),
 col2 varchar2(20),
 col3 number);
insert into T values ('a', 'b', 10);
insert into T values ('a', 'b', 11);
insert into T values ('a', 'b', 13);
insert into T values ('c', 'd', 11);
insert into T values ('c', 'd', 12);写一条SQL文,能检索出如下的效果
COL1            COL2        COL3
a               b           10
                            11
                            13
c               d           11
                            12样子上有点象外连接.
我想了很多办法,比如用select case when之类的,但没有成功

Answer 1

--用BREAK ON 
SQL> BREAK ON DEPTNO 
SQL> SELECT DEPTNO, ENAME, SAL 
     FROM EMP 
     WHERE SAL < 2500 
     ORDER BY DEPTNO; DEPTNO     ENAME       SAL 
---------- ----------- --------- 
10         CLARK       2450 
           MILLER      1300 
20         SMITH       800 
           ADAMS       1100 

Answer 2

SQL> select decode(rn, 1, col1, null) col1, decode(rn, 1, col1, null) col2, col3
  2    from (select col1,
  3                 col2,
  4                 col3,
  5                 row_number() over(partition by col1, col2 order by 1) rn
  6            from t)
  7  ;COL1                 COL2                       COL3
-------------------- -------------------- ----------
a                    a                            10
                                                  11
                                                  13
c                    c                            11
                                                  12

Answer 3

上面写错了一点
SQL> select decode(rn, 1, col1, null) col1, decode(rn, 1, col2, null) col2, col3
  2    from (select col1,
  3                 col2,
  4                 col3,
  5                 row_number() over(partition by col1, col2 order by 1) rn
  6            from t)
  7  ;COL1                 COL2                       COL3
-------------------- -------------------- ----------
a                    b                            10
                                                  11
                                                  13
c                    d                            11
                                                  12

Answer 4

SQL> SELECT * FROM T;COL1                 COL2                       COL3
-------------------- -------------------- ----------
a                    b                            10
a                    b                            11
a                    b                            13
c                    d                            11
c                    d                            12SQL> SELECT DECODE(COL1,LAG(COL1,1)OVER(ORDER BY COL1),NULL,COL1), DECODE(COL2,LAG(COL2,1)OVER(ORDER BY COL2),NULL,COL2),COL3 FROM T;DECODE(COL1,LAG(COL1,1)OVER(OR DECODE(COL2,LAG(COL2,1)OVER(OR       COL3
------------------------------ ------------------------------ ----------
a                              b                                      10
                                                                      11
                                                                      13
c                              d                                      11
                                                                      12

Answer 5

用decode方法，5楼写的很合理，顶上

Answer 6

用decode方法，5楼写的很合理，顶上

Answer 7

LAG 表示 分组排序后 ，组内后面一条记录减前面一条记录的差，第一条可返回 NULL 
那LAG(COL1, 1)是不是表示后一条记录的COL1减去前面一条记录的COL1,那么不就是0吗?
那DECODE(条件, 值1, 结果1, 值2, 结果2, 其它结果)
难道是检测 条件=0?
我感觉LAG(COL1, 1)是返回当前记录的下一条记录的COL1值?是不是这样?

Answer 8

http://hyrongg.download.csdn.net/到我的资源上下载分析函数大全，讲得很好