SQL常用的特殊寫法

連線到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.生成1-10的數字：
SQL> select rownum from dual connect by rownum <11;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

已選擇10行。

2.生成5-10的數字：
SQL> select rownum+5 from dual connect by rownum <6;

  ROWNUM+5
----------
         6
         7
         8
         9
        10

3.生成4個隨機數：
SQL> select dbms_random.random from dual connect by rownum <5;

    RANDOM
----------
-1.272E+09
1091597786
 144717231
1903280455

4.隨機選出2行資料：
SQL> create table test ( id number);

表已建立。

SQL> insert into test values (1);

已建立 1 行。

SQL> insert into test values (11);

已建立 1 行。

SQL> insert into test values (111);

已建立 1 行。

SQL> insert into test values (1111);

已建立 1 行。

SQL> insert into test values (11111);

已建立 1 行。

SQL> insert into test values (111111);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

        ID
----------
         1
        11
       111
      1111
     11111
    111111

已選擇6行。

SQL> select tt.id from (
  2  select t.*,dbms_random.random rd,rownum rn from test t order by rd) tt wher
e rownum<3;

        ID
----------
        11
    111111

5.記錄分組，每組取前3行：
SQL> select * from test;

        ID NAME
---------- --------------------
         1 aaa
         1 bbb
         1 ccc
         1 ddd
         2 dfdjfk
         3 dddddjfk
         4 dddddjfk
         4 j45djfk
         2 39uijdf
         2 333uijdf
         2
         4
         4 j93nmf
         3 j9887eef
         3 j^&*7eef
         3 j^djkieeef
         3 j((ieeef
         4 j899f
         3 ###9f

已選擇19行。

SQL> select *
  2    from (select id, name, row_number() over(partition by id order by id) rn
  3            from test)
  4   where rn <= 3;

        ID NAME                         RN
---------- -------------------- ----------
         1 aaa                           1
         1 bbb                           2
         1 ccc                           3
         2 dfdjfk                        1
         2                         2
         2 333uijdf                      3
         3 dddddjfk                      1
         3 ###9f                         2
         3 j((ieeef                      3
         4 dddddjfk                      1
         4 j45djfk                       2
         4                       3

已選擇12行。

行轉列：
select a,b,c from
(with test as (select 'aaa' a,'bbb' b,'1,2,3' c from dual)
select a,b,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select a,b,',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt )

子查詢定義：
http://www.itpub.net/viewthread.php?tid=1235717