DB2 SQL查詢結果集自動編号、傳回條數範圍控制研究

摘要：

DB2 SQL語句實作查詢的記錄自動編号、并根據編号進一步實作記錄範圍控制、分頁查詢。

環境：

IBM DB2 Universal V9.1

Windows XP Professional

要求：寫一個SQL語句，在查詢結果中要顯示出記錄序号，這個序号是是根據記錄順序自動生成的。

測試初始化DB2 SQL腳本：

drop table t_test;

create table t_test

 (bs        bigint          not null  not null generated by default as identity,

  username  varchar(20)     not null,

  password  varchar(20),

  remark    varchar(200)

 );

comment on t_test

 (bs is '辨別',

  username is '使用者名',

  password is '密碼',

  remark is '備注'

insert into t_test(username, password) values ('aaa', 'aaa'),

('bbb', 'bbb'),

('ccc', 'ccc'),

('ddd', 'ddd'),

('eee', 'eee'),

('fff', 'fff'),

('ggg', 'ggg');

實作：

1、實作查詢記錄編号

要求：查詢bs不等于1的所有記錄。

select t.*, ROW_NUMBER() OVER() as ROW_NO

 from t_test t

where t.bs <> 1

查詢結果如下：

bs  username password remark    ROW_NO 

---------------------------------------

2   'bbb'   'bbb'   '[Null]'    1

3   'ccc'   'ccc'   '[Null]'    2

4   'ddd'   'ddd'   '[Null]'    3

5   'eee'   'eee'   '[Null]'    4

6   'fff'   'fff'   '[Null]'    5

7   'ggg'   'ggg'   '[Null]'    6

說明：ROW_NUMBER() OVER() 是用來産生編号序列，從1開始編号，為整數。

as ROW_NO 是為編号列設定顯示名稱。

2、實作按照記錄範圍進行查詢

要求：要求查詢第2-5條記錄。

select *

  from (select t.*, ROW_NUMBER() OVER() as ROW_NO

          from t_test t) as w

 where ROW_NO >= 2

 and ROW_NO <=5

2   'bbb'   'bbb'   '[Null]'    2

3   'ccc'   'ccc'   '[Null]'    3

4   'ddd'   'ddd'   '[Null]'    4

5   'eee'   'eee'   '[Null]'    5

3、實作SQL分頁查詢

對2中的SQL進行擴充，将範圍起始編号作為變量傳遞給SQL就可以實作分頁查詢了。當然這個還需要程式的支援，掌握這個原理就很容易了，在此不做介紹。

總結：

對記錄傳回控制是資料庫系統的特性，不同的資料庫系統有不同的實作。SQL直接對結果集記錄控制效率上一般是最好的。結合進階程式設計語言，可以實作複雜的分頁查詢。

本文轉自 leizhimin 51CTO部落格，原文連結：http://blog.51cto.com/lavasoft/27794，如需轉載請自行聯系原作者