Web應用經常會出現分頁顯示結果集的場景，如果處理不好將會嚴重影響應用程式的效能。如何突破這個瓶頸呢？
透過一個需求演示場景給大家展示兩種實現方法，孰優孰劣看後便知。

1.建立實驗表T，並初始化一些實驗資料
sec@ora10g> create table t (id number, sal number);
sec@ora10g> insert into t values (1,1000);
sec@ora10g> insert into t values (2,800);
sec@ora10g> insert into t values (3,600);
sec@ora10g> insert into t values (4,2000);
sec@ora10g> insert into t values (5,3000);
sec@ora10g> insert into t values (6,1500);
sec@ora10g> insert into t values (7,1800);
sec@ora10g> insert into t values (8,2100);
sec@ora10g> insert into t values (9,400);
sec@ora10g> insert into t values (10,600);
sec@ora10g> insert into t values (11,200);
sec@ora10g> insert into t values (12,1900);
sec@ora10g> insert into t values (13,3000);
sec@ora10g> insert into t values (14,4000);
sec@ora10g> commit;

2.T表資料概覽
sec@ora10g> select * from t;

        ID        SAL
---------- ----------
         1       1000
         2        800
         3        600
         4       2000
         5       3000
         6       1500
         7       1800
         8       2100
         9        400
        10        600
        11        200
        12       1900
        13       3000
        14       4000

14 rows selected.

3.實現需求一：按照工資升序排序後的第1名到第3名資訊
1）錯誤方法
sec@ora10g> select * from t where rownum<=3 order by sal;

        ID        SAL
---------- ----------
         3        600
         2        800
         1       1000

這種查詢方法是先獲得T表中的前三條記錄，然後對這三條記錄的sal值進行排序。與我們的需求不符。

2）正確方法一
使用子查詢與rownum完成任務。
sec@ora10g> select * from (select * from t order by sal) where rownum<=3;

        ID        SAL
---------- ----------
        11        200
         9        400
         3        600

3）正確方法二
使用分析函式ROW_NUMBER完成此任務。
sec@ora10g> select id, sal
2    from (select id, sal, ROW_NUMBER () over (order by sal) as rn
3            from t)
4 where rn <= 3;

        ID        SAL
---------- ----------
        11        200
         9        400
         3        600

4.實現需求二：按照工資升序排序後的第4名到第6名資訊
1）錯誤的方法
sec@ora10g> select * from (select * from t order by sal) where rownum between 4 and 6;

no rows selected

rownum的這種使用方法是不正確的！要堅決抵制。

2）正確方法一
用rownum來構造這個結果集
sec@ora10g> select *
2    from (select * from (select * from t order by sal)
3           where ROWNUM <= 6
4          minus
5          select * from (select * from t order by sal)
6           where ROWNUM <= 3)
7 order by sal;

        ID        SAL
---------- ----------
        10        600
         2        800
         1       1000

可見，使用這種集合減構造出來的結果集的方法稍顯笨重。

3）正確方法二
使用分析函式ROW_NUMBER可以很便捷的完成這個任務。
sec@ora10g> select id, sal
2    from (select id, sal, ROW_NUMBER () over (order by sal) as rn
3            from t)
4 where rn between 4 and 6;

        ID        SAL
---------- ----------
        10        600
         2        800
         1       1000

5.以此類推，便可以實現任意指定範圍內的統計資料查詢。

6.小結
“正確方法一”給出的方法雖然可以實現我們的需求，但是獲取資料的代價較大，其間存在大量的排序操作。
“正確方法二”使用分析函式先對第一種方法效率上得到了保證，同時給SQL的編寫帶來很大的靈活性，推薦使用。
透過這個例子我們再一次體會到了分析函式帶給我們的便利和高效。

Good luck.

secooler
10.02.06

-- The End --

【SQL】獲取指定範圍內結果集的實現方法

相關文章