這個實驗將會得出如下幾個結論：
1. Oracle 10g資料庫以select * 方式獲取資料的順序基本上是以初始化到資料庫的順序顯示的；
2. Oracle 10g資料庫以select * 方式獲取資料不會進行排序，只有在使用order by的情況下才能保證資料的順序；
3. 若實現rownum與資料遞增順序保持同步，這裡給出一個使用子查詢的方式的實現方式。

實驗BEGIN：
1.建立一個簡單的測試表t，只包含一列x
sec@ora10g> create table t (x varchar2(30));

Table created.

2.初始化測試表t的資料，注意這裡資料初始化順序（a，d，c，b）
sec@ora10g> insert into t values ('a');
sec@ora10g> insert into t values ('d');
sec@ora10g> insert into t values ('c');
sec@ora10g> insert into t values ('b');
sec@ora10g> commit;

3.驗證結論1和2，select * 方式獲取資料的順序基本上是以初始化到資料庫的順序顯示的，沒有順序
sec@ora10g> select * from t;

X
------------------------------
a
d
c
b

4.觀察一下此時rownum和資料的順序關係
sec@ora10g> select rownum,t.* from t;

    ROWNUM X
---------- ------------------------------
         1 a
         2 d
         3 c
         4 b

5.對資料列x進行主動排序後，觀察一下rownum與資料列x的順序，這裡rownum的順序是混亂的
sec@ora10g> select rownum,t.* from t order by 2;

    ROWNUM X
---------- ------------------------------
         1 a
         4 b
         3 c
         2 d

6.實現結論3，可以透過下面這個子查詢的方法得到一個rownum與有序的資料列x保持一致的遞增順序
sec@ora10g> select rownum,tt.x from ( select x from t order by x) tt;

    ROWNUM X
---------- ------------------------------
         1 a
         2 b
         3 c
         4 d

7.如何在實際中應用這個實驗結論
舉一個利用這個結論來解決最近專案組的一個SQL需求的例子。
需求如下：要求按照x的遞增順序，更新描述欄位為“DESCRIPTION-0001”樣式的遞增內容，“DESCRIPTION-”是固定的，“0001”是四位表示的序列號
實現需求如下：
1）在t表中新增一個描述欄位y
sec@ora10g> alter table t add y varchar2(30);

Table altered.

2）使用如下的SQL語句即可完成需求（如果覺得這個SQL不好理解的話，可以先建立一箇中間表，再關聯這個中間表更新表t，其實下面的SQL思路是一樣的，只是使用了一條SQL語句來實現）
sec@ora10g> UPDATE t
2     SET y = (SELECT tt.y
3                FROM (SELECT x, 'DESCRIPTION-' || LPAD (ROWNUM, 4, '0') y
4                        FROM (SELECT   x
5                                  FROM t
6                              ORDER BY x)) tt
7               WHERE tt.x = t.x);

4 rows updated.

3）最後確認資料已經按照需求修改成功
sec@ora10g> select * from t;

X                              Y
------------------------------ ------------------------------
a                              DESCRIPTION-0001
d                              DESCRIPTION-0004
c                              DESCRIPTION-0003
b                              DESCRIPTION-0002

透過上面的這種方法就可以既利用到了rownum，又避免因預設無法排序問題導致更新錯誤的問題了。

-- The End --

【實驗】【SQL】排序與rownum的關係暨利用rownum處理一實際需求

相關文章