跨越Oracle和MSSQL关系数据库开发 -- 02 Oracle和MSSQL返回结果集区间的方法

来源：互联网发布：2009nba体测数据编辑：程序博客网时间：2024/04/30 04:59

1. 概述

　　在这个专题中，我想讲述一个比较复杂的话题，这个专题非常实用，希望读者可以耐心的看完。

使用SQL语句操作从第n行到m行数据是一项很普通的任务。但是由于不同数据库对于结果集操作方式不同，经常会引起一些误解。另外，从性能和代码的方便性来看，这个问题值得做一番研究。

　　本文中对常见的陷阱做了分级，星号越多，该陷阱就越复杂。

2. Oracle中如何取第m行到n行数据

2.1 样表

　　本节使用一个简化的证券买卖流水表，读者可在SQL PLUS中直接执行以下代码：

create table fsjour

(

serial_no NUMBER(12) NOT NULL, /*流水号 */

branch_no NUMBER(8) NOT NULL, /*营业部号*/

fund_account NUMBER(12) NOT NULL, /*资金帐号*/

occur_balance NUMBER(19,2) NOT NULL, /*发生金额*/

stock_code char(6) NOT NULL, /*股票代码*/

status_code char(1) NOT NULL, /*流水状态*/

constraint pk_fsjour primary key(serial_no)

);

create index idx_fund_account on fsjour(fund_account);

create index idx_stock_code on fsjour(stock_code);

insert into fsjour values(1, 1, 1000881, 12000, '600345', '0');

insert into fsjour values(2, 1, 1007049, 32056, '600740', '0');

insert into fsjour values(3, 1, 1100032, 1245, '600101', '0');

insert into fsjour values(9, 3, 1090801, 93001, '600900', '0');

insert into fsjour values(5, 1, 1045088, 83000, '600371', '0');

insert into fsjour values(4, 1, 1279602, 23466, '600865', '0');

insert into fsjour values(6, 4, 1001003, 38441, '600267', '0');

insert into fsjour values(7, 1, 1003451, 9870, '600541', '0');

insert into fsjour values(8, 1, 1098064, 34844, '600638', '0');

insert into fsjour values(16, 1, 1098064, 34844, '600638', '0');

insert into fsjour values(19, 1, 1000881, 12600, '600345', '0');

insert into fsjour values(10, 1, 1007049, 32006, '600840', '0');

insert into fsjour values(11, 1, 1100032, 12459, '600501', '0');

insert into fsjour values(13, 2, 1090801, 43001, '600903', '0');

insert into fsjour values(12, 1, 1045088, 23100, '000717', '0');

insert into fsjour values(14, 1, 1279602, 50961, '000813', '0');

insert into fsjour values(15, 3, 1001003, 67343, '600834', '0');

insert into fsjour values(18, 1, 1003451, 98701, '600002', '0');

insert into fsjour values(17, 2, 1098064, 9034, '600098', '0');

insert into fsjour values(20, 1, 1098064, 43439, '600718', '0');

insert into fsjour values(21, 1, 1098064, 93483, '600438', '0');

commit;

2.2 取前n行

　　Oracle中通常使用rownum这个伪列来取前n行数据。

　　所谓“前n行”结果集，是指将数据按照某种规则进行排序，然后取最前面的n行，用区间表示为[1, n]，是[m, n]的一种特例。

　　取前n行结果的方法很简单：在SQL的结果集上加上条件进行过滤：

rownum <=n

　　即可。

　　例如，我们希望取出上述表中交易金额最多的10笔流水，结果集中交易金额越大，排名越前。

　　注意，上面说到是在SQL的结果集上加上条件进行过滤，以下是正确语句：

select * from (select * from fsjour order by occur_balance desc)

where rownum <= 10;

　　陷阱一(*)：

select * from fsjour where rownum <= 10

order by occur_balance desc;

　　该语句的真实含义是：

　　首先选择fsjour表中前10条记录，然后按照交易金额进行降次排序。

　　这是因为在SQL语句中，WHERE部分总是先于ORDER BY部分执行。所以，其　　　结果是：

SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

9 3 1090801 93001.00 600900 0

5 1 1045088 83000.00 600371 0

6 4 1001003 38441.00 600267 0

8 1 1098064 34844.00 600638 0

2 1 1007049 32056.00 600740 0

4 1 1279602 23466.00 600865 0

19 1 1000881 12600.00 600345 0

1 1 1000881 12000.00 600345 0

7 1 1003451 9870.00 600541 0

3 1 1100032 1245.00 600101 0

2.3 取m行到n行

　　当m大于1时，rownum的使用将变得复杂，通常需要使用二层以上子查询才能实现。

　　例如，我们希望取出上述表中交易金额最多的第11笔到20笔流水，结果集中交易金额越大，排名越前。

　　以下是正确语句（当然正确的语句并不是唯一的）：

select *

from (select rownum as ord, t.*

from (select *

from fsjour

order by occur_balance desc) t)

where ord between 11 and 20;

　　陷阱二(*)：

　　初学者往往使用以下语句来实现：

select *

from (select *

from fsjour

order by occur_balance desc)

where rownum between 11 and 20;

　　很不幸，以上语句返回的结果集始终包含0行数据。

　　陷阱三(**)：

　　以下语句看起来已经比较复杂，但是是错误的。

select *

from (select rownum as ord, j.*

from fsjour j

order by occur_balance desc)

where ord between 11 and 20;

　　其结果是：

ORD SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

17 18 1 1003451 98701.00 600002 0

20 21 1 1098064 93483.00 600438 0

16 15 3 1001003 67343.00 600834 0

15 14 1 1279602 50961.00 000813 0

19 20 1 1098064 43439.00 600718 0

13 13 2 1090801 43001.00 600903 0

11 10 1 1007049 32006.00 600840 0

14 12 1 1045088 23100.00 000717 0

12 11 1 1100032 12459.00 600501 0

18 17 2 1098064 9034.00 600098 0

　　可以看出，结果集中的ord其实根本不是真实的排名。

　　有些开发人员可能还会采用另外一种方法来获取m行到n行记录，他们采用ORACLE的集合操作中的减法。例如，本小节中的例子可以用以下语句来查询：

select *

from (select * from (select * from fsjour order by occur_balance desc)

where rownum <= 20

minus

select * from (select * from fsjour order by occur_balance desc)

where rownum <= 10)

order by occur_balance desc;

　　这种方法运用两次前n行记录，虽然能够得到正确的结果，但是效率低下，建议不使用。

2.4 rownum探密

　　关于rownum的基本原理，各种参考书上都进行了说明。这里对此作一个概括。

1. 当rownum出现在where部分时，ORACLE搜索引擎对结果集从第一行进行rownum过滤时，一旦发现当前行不符合rownum条件即终止其余行的处理并返回。

2. 当rownum与order by在同一子句中时，无论rownum出现在select部分还是where部分，rownum表示的均是未排序之前的行号。

3. 当Where条件中包含其他条件时，先计算其他条件，其结果集的行号被赋予rownum。

　　经验1说明了陷阱二。在用条件“rownum between 11 and 20”进行结果集筛选时，Oracle发现第一行与之不匹配，因为第一行的rownum是1。

　　经验2说明了陷阱三。由于ord是排序之前的行号，因此不能用它来获取我们需要的排行数据。

2.5 取任意一行

　　很多时候，当程序处理数据时，只需要获取满足条件的任意一条记录即可，无须进行排序。这时，语句相当简单。

　　例如，我们希望获取上述表中状态为’00’的任何一条记录进行处理，语句如下：

select * from fsjour where status_code = ‘0’ and rownum =1;

　　该语句正确的返回了一条记录，其根据是经验3。

　　这里之所以把取任意一行记录单独成节，绝不是因为要强调经验3，而是为了介绍一个十分隐密的陷阱。

　　陷阱四(***)：

select * from fsjour where status_code = ‘0’ and rownum =1 for update;

　　该语句用于任意选择一行数据，然后将其锁住，以便当前线程进行处理时不会被其他线程干扰。

　　如果你在sqlplus或者其他一些工具中运行该语句将不会得到任何错误。但是，如果你使用JDBC来执行，那么会得到语句执行错误的消息。JDBC不允许该种类型的语句，所以必须使用一定的技巧来绕开限制。下面就是一个实现：

select t.* from fsjour t

where serial_no = (select serial_no from fsjour

where status_code = '0' and rownum = 1)

for update;

通常，当表有主键时可以使用主键，否则可以使用rowid。

这里顺便提供给读者另一个不易察觉的JDBC2.0问题。该问题的描述在Oracle 9i的联机手册有说明。

在上面的SQL语句中，读者是否注意到我使用了一个累赘的语法：

select t.* from fsjour t

我相信多数读者通常会用

select * from fsjour

来代替前面的语句。但是，在特定的情况，这种看似丝毫不差的更改在某种特殊的情况下是不可以的：当创建可更改的结果集时，不能使用select *，而必须使用上面的特殊语法。

为何会这样一来呢？原来在创建CONCUR_UPDATABLE类型的结果集时，JDBC2.0会使用ROWID来记住每行列，它采用的方式是向SQL语句中添加ROWID列，因此，如果将SQL语句定义成“select * from table”，那么转换后的语句变成“select *, ROWID from table”，这会产生非法语句。

关于JDBC2.0的问题还没有结束。笔者在项目中曾经遇到过关于使用可修改ResultSet的更难以解决的问题。

问题起源于一个比较复杂的查询语句：

select t.* from BATCH_SUB_REQUEST t

where (BSR_request_id, BSR_item) =

(select BSR_request_id, BSR_item

from BATCH_SUB_REQUEST bsr

where bsr.BSR_item =

(select min(bsr2.BSR_ITEM)

from BATCH_SUB_REQUEST bsr2

where (bsr2.BSR_STATUS= 'new' or

(bsr2.BSR_STATUS='processing' and

bsr2.BSR_UPDATE_DATE<sysdate-1/24/6))

and bsr2.BSR_request_id = bsr.BSR_request_id)

and rownum=1)

for update

该语句的目的很明确，找到任意一条满足条件的记录，锁定之。并且已经注意到了前面所述的问题。

　　上面的语句中，(BSR_request_id, BSR_item)是表的主键。

以下是一段模拟实际应用程序行为的的Java代码：

PreparedStatement st = conn.prepareStatement

(GET_UNPROCESSED_SUBREQUEST_ANY,

ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet rs = st.executeQuery();

if (rs.next()) {

//do something else

rs.updateTimestamp("BSR_UPDATE_DATE", currentTimeStamp);

//do commit and else

}

这段代码在系统交付使用后相当一段时间内没有任何问题。突然有一天，客户向我报告在updateTimestamp语句出出现了异常错误，并且随后该异常并不再现。之后几天中每天有一两次异常报告。

java.sql.SQLException: Invalid operation for read only resultset: updateTimestamp

随后展开的调查结果令人大吃一惊：这居然是一个JDBC2.0的缺陷。也就是说上面看似完全正确的代码实际上是不可靠的。分析如下：

当使用ResultSet的updateXXX功能时，其目的是修改某个记录的字段。在上面的代码中，使用了for update来锁定一个记录，使当前数据库连接获取了一个排他锁。然而完全出乎意料的是，在发生异常时，updateXXX试图修改的记录竟然不是前面锁定的那条！

听起来不可思议。随着分析的深入，真相逐渐被揭露出来。

我跟踪了JDBC自动转化后的SQL，发现查询语句被转换成以下的形式：

select rowid, t.*

from BATCH_SUB_REQUEST t

where (BSR_request_id, BSR_item) =

(select BSR_request_id, BSR_item

from BATCH_SUB_REQUEST bsr

where bsr.BSR_item =

(select min(bsr2.BSR_ITEM)

from BATCH_SUB_REQUEST bsr2

where (bsr2.BSR_STATUS = 'new' or

(bsr2.BSR_STATUS='processing' and

bsr2.BSR_UPDATE_DATE<sysdate-1/24/6))

and bsr2.BSR_request_id = bsr.BSR_request_id)

and rownum=1)

for update

也就是说，JDBC需要使用ROWID来标识被锁定的行。这种方式是正确的，因为Oracle给每个数据行赋予了唯一固定的行标识。除非行被删除，否则行标识是不变的。

此时，在我的想象中，似乎JDBC在进行修改字段时，可以这样简单的写：

update BATCH_SUB_REQUEST

set col_name = value

where rowid = v_rowid

然而跟踪的结果却不是这样的，而是如下所示：

update (select rowid, t.*

from BATCH_SUB_REQUEST t

where (BSR_request_id, BSR_item) =

(select BSR_request_id, BSR_item

from BATCH_SUB_REQUEST bsr

where bsr.BSR_item =

(select min(bsr2.BSR_ITEM)

from BATCH_SUB_REQUEST bsr2

where (bsr2.BSR_STATUS = 'new' or

(bsr2.BSR_STATUS='processing' and

bsr2.BSR_UPDATE_DATE<sysdate-1/24/6))

and bsr2.BSR_request_id = bsr.BSR_request_id)

and rownum=1)

set BSR_UPDATE_DATE = '07/25/2005 01:26:21'

where ROWID = 'AAAJ/OAAiAABQvzABE'

也就是说是对一个视图进行修改。

咋一看，这个语句也是对的，因为视图表示的结果集不就是等于ROWID所表示的记录吗？只是好象两者重复了，有些累赘。

不知道细心的读者是否已经发现，同一个查询语句（特别是含有rownum的语句）运行两遍是不能保证返回同样的结果的。对于事务了解透彻的读者会争辩道在同一个事务内同一个查询语句返回的结果集是一样的，除非事务本身修改之。

但是，这里运行的是同一个语句吗？答案呼之欲出。

在前面的查询语句的结尾，我们还多了一个“for update”。正是这个指令，使得Oracle在数据比较复杂时会选择与没有该指令的语句不同的执行方案，从而导致结果不一致。

开发人员无法对这种JDBC和Oracle的行为进行控制，但是可以采取其他方法来解决这个问题。笔者采用的方法很简单，首先在查询到记录后，保存其主键值，然后在修改表时用主键值作为条件。当然，也可以保存ROWID值，不过通常在应用程序中不建议直接使用ROWID。

2.6 Oracle的增强功能

2.6.1 使用rank()替代rownum

　　使用rownum来获取排行数据历来已久，但是这种方法不仅代码复杂，而且排行数据不尽正确。例如上面的数据中，我们发现流水号16和8的金额一样，但是一个被排行成第11名，另一个却是第12名。

　　为了解决排行名次问题，并且简化rownum带来的复杂行，Oracle引进了RANK()函数。另外还有一个DENSE_RANK()函数。RANK()函数进行排行，如果有相同名次则后续名次被跳过。而DENSE_RANK()函数如果遇到相同名次，则将后面的名次提前。

　　运行以下语句可以看出差别：

select RANK() over (order by occur_balance) as ord,

DENSE_RANK() over (order by occur_balance) as ord_dense,

t.* from fsjour t;

　　结果集为：

ORD ORD_DENSE SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

1 1 3 1 1100032 1245.00 600101 0

2 2 17 2 1098064 9034.00 600098 0

3 3 7 1 1003451 9870.00 600541 0

4 4 1 1 1000881 12000.00 600345 0

5 5 11 1 1100032 12459.00 600501 0

6 6 19 1 1000881 12600.00 600345 0

7 7 12 1 1045088 23100.00 000717 0

8 8 4 1 1279602 23466.00 600865 0

9 9 10 1 1007049 32006.00 600840 0

10 10 2 1 1007049 32056.00 600740 0

11 11 8 1 1098064 34844.00 600638 0

11 11 16 1 1098064 34844.00 600638 0

13 12 6 4 1001003 38441.00 600267 0

14 13 13 2 1090801 43001.00 600903 0

15 14 20 1 1098064 43439.00 600718 0

16 15 14 1 1279602 50961.00 000813 0

17 16 15 3 1001003 67343.00 600834 0

18 17 5 1 1045088 83000.00 600371 0

19 18 9 3 1090801 93001.00 600900 0

20 19 21 1 1098064 93483.00 600438 0

21 20 18 1 1003451 98701.00 600002 0

　　可以看到RANK()跳过了第12名。

　　使用RANK()可以减少子查询层数。例如，我们希望取出上述表中交易金额最多的第11笔到20笔流水，结果集中交易金额越大，排名越前。

　　可以使用以下语句：

select *

from (select rank() over (order by occur_balance desc ) as ord,

fsjour.*

from fsjour)

where ord between 11 and 20;

　　其结果集是：

ORD SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

12 2 1 1007049 32056.00 600740 0

13 10 1 1007049 32006.00 600840 0

14 4 1 1279602 23466.00 600865 0

15 12 1 1045088 23100.00 000717 0

16 19 1 1000881 12600.00 600345 0

17 11 1 1100032 12459.00 600501 0

18 1 1 1000881 12000.00 600345 0

19 7 1 1003451 9870.00 600541 0

20 17 2 1098064 9034.00 600098 0

　　此时只有9条记录返回，没有第11名，按照预先定义的排名规则，这是合理的。

　　RANK()的语法被定义的很复杂，它用多种用途。下面介绍几种除上面已经介绍过之外的常见用法。

2.6.2 应用于group by

　　例如，我们希望得到不同营业部的交易金额排名，可以使用以下语句：

select rank() over (order by sum(occur_balance) desc) ord,

sum(occur_balance), branch_no

from fsjour

group by branch_no;

　　其结果集是：

ORD BALANCE BRANCH_NO

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

1 598074 1

2 160344 3

3 52035 2

4 38441 4

2.6.3 应用于组内排名

　　执行以下语句：

select rank() over (partition by branch_no

order by occur_balance desc) as ord_in_branch,

t.*

from fsjour t;

　　其得到的结果是什么呢？

ORD_IN_BRANCH SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

1 18 1 1003451 98701.00 600002 0

2 21 1 1098064 93483.00 600438 0

3 5 1 1045088 83000.00 600371 0

4 14 1 1279602 50961.00 000813 0

5 20 1 1098064 43439.00 600718 0

6 8 1 1098064 34844.00 600638 0

6 16 1 1098064 34844.00 600638 0

8 2 1 1007049 32056.00 600740 0

9 10 1 1007049 32006.00 600840 0

10 4 1 1279602 23466.00 600865 0

11 12 1 1045088 23100.00 000717 0

12 19 1 1000881 12600.00 600345 0

13 11 1 1100032 12459.00 600501 0

14 1 1 1000881 12000.00 600345 0

15 7 1 1003451 9870.00 600541 0

16 3 1 1100032 1245.00 600101 0

1 13 2 1090801 43001.00 600903 0

2 17 2 1098064 9034.00 600098 0

1 9 3 1090801 93001.00 600900 0

2 15 3 1001003 67343.00 600834 0

1 6 4 1001003 38441.00 600267 0

原来是首先按照营业部号码进行分组，然后在每个大组内按照交易金额进行排序。这中特别的功能对于制作报表者实在是太有用了。

此时，要获取每个营业部的前两名是非常简单明了的。

select * from

(select rank() over (partition by branch_no

order by occur_balance desc) as ord_in_branch,

t.*

from fsjour t)

where ord_in_branch <= 2;

3. Oracle的分页显示

3.1 根据指定行号取当前页数据

　　例如，如果一页最多显示5行数据，那么第三页就是显示第11行到第15行数据。这相当于2.3节中所描述的那样。

　　这种方式的缺点是使用了表扫描，而且还要排序，效率低下。如果表中有大量数据，这种方式根本不可取。

3.2 根据索引依次向后取n行

　　提高查询效率的基本方法是使用索引。例如，我们希望按照流水号次序来翻页，那么，可以使用以下语句：

select * from fsjour where serial_no > ? and rownum <= 5;

　　其中的?表示前一页的结束编号。

　　在我们的例子中，第一页可以使用0，第二页则使用第一页的最后一个记录的值，即5。让我们来看第二页的输出：

SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

6 4 1001003 38441.00 600267 0

7 1 1003451 9870.00 600541 0

8 1 1098064 34844.00 600638 0

9 3 1090801 93001.00 600900 0

10 1 1007049 32006.00 600840 0

　　我们欣喜的发现，虽然我们没有使用order by，但是结果已经是排序的了（注意，有时你会发现这里不是这样的，必须用索引提示才行）。这是因为当前语句使用索引进行扫描，而索引在默认情况下是按照索引列的值进行升序排序的。为了避免进行排序，必须确保语句使用正确的索引。对于复杂的SQL应该加上HINT来确保正确索引的使用。

　　这里必须提醒读者，如果优化器不使用指定索引进行检索，而是使用全表扫描或者其他索引，那么返回的结果集很可能不是排序的，所以建议一定使用索引提示。

　　读者可能对上述说法产生怀疑。实际上，这个结果很合理。在索引值相同时，ORACLE则根据ROWID来排序，越小的排名越前。如果你翻页时如果使用的索引不是唯一索引，那么请在条件中加上ROWID，不然，如果两条记录正好索引值相同，且跨页时就会出现同一记录重复出现在不同页上，或者丢失。

3.3 根据索引依次向前取n行

　　如果从当前页翻到前面一页，输入的是当前页的第一行，例如，是11，这时，要取其前面的5行。这时，利用前面的方法来实现显得有点复杂，并且效率低下：

select *

from (select *

from fsjour

where serial_no < 11 order by serial_no desc)

where rownum <=5)

order by serial_no;

　　读者不妨自己也写一个语句实现一下该功能。

　　这里提醒读者可以使用以下语句来简化之：

select /*+ INDEX_DESC(fsjour, pk_fsjour)*/

* from fsjour

where serial_no < 11 and rownum <= 5

order by serial_no;

　　返回同样是：

SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE

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

6 4 1001003 38441.00 600267 0

7 1 1003451 9870.00 600541 0

8 1 1098064 34844.00 600638 0

9 3 1090801 93001.00 600900 0

10 1 1007049 32006.00 600840 0

　　这里关键是索引提示INDEX_DESC，这提示反向搜索。这里rownum与order by处在同一层依然奏效，因为其正好不违反经验2。

　　这里，我建议开发人员将已经读取的数据缓存在本地，这样，在向前回滚页面时就可以不与数据库进行交互。当然，能否可行要视数据行为而定。

3.4 索引包含多列时翻页

　　当索引包含多列时，ORACLE通常不能自动使用目标索引。并且由于条件复杂，必须注意如何合理的描述查询语句条件。

　　例如，如果有一个索引：

create index idx_branch_stock on fsjour(branch_no, stock_code);

　　按照该索引进行向下翻页，语句可写成：

select j.*, rowid from fsjour j

where (branch_no > ?

or branch_no = ? and stock_code >=?)

and rowid > ?

and rownum <= 5;

　　请读者注意，这里必须加上rowid的原因是索引并不唯一。

　　第一页时，branch_no的比较值可以取0，而stock_code的比较值可以取NULL，而rowid可以取’AAAAAAAAAAAAAAAAAA’。

　　这样第一页的语句为：

select j.*, rowid from fsjour j

where (branch_no > 0

or branch_no = 0 and stock_code >= ‘’)

and rowid > 'AAAAAAAAAAAAAAAAAA'

and rownum <= 5;

　　其结果是：

SERIAL_NO BRANCH_NO FUND_ACCOUNT OCCUR_BALANCE STOCK_CODE STATUS_CODE ROWID

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

1 1 1000881 12000.00 600345 0 AAAHekAABAAAMfSAAA

2 1 1007049 32056.00 600740 0 AAAHekAABAAAMfSAAB

3 1 1100032 1245.00 600101 0 AAAHekAABAAAMfSAAC

9 3 1090801 93001.00 600900 0 AAAHekAABAAAMfSAAD

5 1 1045088 83000.00 600371 0 AAAHekAABAAAMfSAAE

　　很明显，结果并不按照branch_no和stock_code的组合进行排序。

　　我们使用EXPLAIN PLAN来查看语句的执行方案：

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

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

| 0 | SELECT STATEMENT | | | | |

|* 1 | COUNT STOPKEY | | | | |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<=5)

2 - filter("J".ROWID>'00000000.0000.0000' AND ("J"."BRANCH_NO"

>0 OR "J"."BRANCH_NO"=0 AND "J"."STOCK_CODE">' '))

Note: rule based optimization

　　这说明索引idx_branch_stock根本没有用上。因此，我们必须明确加上索引提示。这样，正确的语句可以为：

select /*+ INDEX(j idx_branch_stock)*/ j.*, rowid from fsjour j

where (branch_no > ?

or branch_no = ? and stock_code >=?)

and rowid > ?

and rownum <= 5;

　　此时结果就符合需要了。

　　当索引中含有更多列时，条件可能相当复杂，此时，有些开发人员会使用组合值来简化语句。例如，上面语句中如果将branch_no和stock_code作为字符串加起来，那么就不需要使用or。但是如何相加是很有技术性的。直接相加肯定是不行的。通常可能要对列进行一些格式化。例如，这里通常会将branch_no格式化成一定长度，例如，如果最长为6，那么在不足的值前补充零。

select /*+ INDEX(j idx_branch_stock)*/ j.*, rowid from fsjour j

where lpad(to_char(branch_no), 6, ‘0’) || stock_code >= ?

and rowid > ?

and rownum <= 5;

此时，ORACLE基本是无法自动识别使用索引的，除非使用函数索引。

3.5 BBS的翻页模式

阅读本书的读者中曾经开发或者已经开发过类BBS模式的网站的人数一定不少。现代BBS功能复杂，早已不是上世纪九十年代刚刚在国内兴起时那般朴素。对于一个日流量成千上日贴的论坛，翻页技术是不可避免的。

由于现代BBS的功能繁多，支持的排序方式也很多。这里我介绍两种排序方式：一是按照发贴时间浏览时进行排序分页，二是按照贴子主题进行浏览时的分页。

本节讲述的原理虽然用Oracle的代码进行描述，但是同样适用于MSSQL的情况。

本文推荐的方法主要针对流量较大的论坛。对于小规模的论坛，不用如此麻烦，采用纯粹的语句技巧进行分页已经足够，参见本章2.3节。

3.5.1 按照发贴时间浏览

相信所有的读者都经历过在BBS或者其他论坛上疯狂灌水的日子。灌水高峰时节，您一分钟前刚提交的贴子可能已经排到几十页后面去了。这种现象说明在BBS上的分页显示并不能用前面所提到的索引方法，而是使用纯粹的按照记录数和每页显示行数进行搜索。

让我们建一个最简单的贴子表：

create table article

(

forum_id number(4) not null, -- 论坛编号

seq_id number(12) not null, -- 序列号

title varchar2(128) not null, -- 文章标题

author varchar2(32) not null, -- 文章作者

publish_time date not null, -- 发表时间

content clob not null -- 文章内容

);

create unique index idx_article on article(forum_id, seq_id);

其中的seq_id作为流水号，在Oracle中用sequence对象产生。我们假设每个论坛的起始文章编号都是S，流水号每次以1为单位进行递增。

请看下面的例子：假如客户端发过来一个请求，要求查找论坛F的第P页的文章，并且客户端的显示界面每页最多只能显示N行记录。

我们可以采用下面的语句来返回结果：

select max(seq_id) into v_record_num from article where forum_id = F;

select v_record_num-(P-1)*N, v_record_num-P*N+1

into v_start_id, v_end_id

from dual;

select * from article /*+ INDEX_DESC(article, idx_article)*/

where forum_id = F

and seq_id beteen v_start_id and v_end_id;

上面的三个语句中前两个用来准备数据，第一个用于获得当前论坛的全部文章数量，第二个则算出本次将要返回的记录范围。最后使用一个简单的语句进行返回，该语句可以充分使用索引进行工作。

3.5.2 删除文章

在各种论坛上，经常需要进行各种理由的维护：例如删除含有反动、颜色类内容的文章，或者是服务器负载过高，需要将有价值的文章整理进精华区，而将时间过长无价值的文章删除。

通常，这样的站点维护期间，论坛的暂时不开放的。

我们发现，上一节所论述的方法，严格的依赖于所有文章从编号1开始逐一递增。一旦我们删除了其中的部分记录，那么返回的结果就不准确了。这里可以采用压缩空洞的方法来消除删除记录造成的影响，具体方法可以参考“流水号的实现”一章。

对于一个不间断运行的论坛，采用这种先删除后压缩的方式显然是不可取的。本人推荐的方法是在上面的表中增加一个字段，作为真正的分页编号，而流水号则允许含有空洞。

create table article

(

forum_id number(4) not null, -- 论坛编号

seq_id number(12) not null, -- 序列号

paging_id number(12) not null, -- 分页用编号

title varchar2(128) not null, -- 文章标题

author varchar2(32) not null, -- 文章作者

publish_time date not null, -- 发表时间

content clob not null -- 文章内容

);

create unique index idx_article on article(forum_id, seq_id);

create unique index idx_article_paging on article(forum_id, paging_id);

假设现在表中有5万条论坛F的记录，现在管理人员发现第2309篇文章宣传非法内容，需要将其删除，那么可以用以下三个语句完成之：

select max(paging_id) into v_record_num from article where forum_id = F;

delete from article

where forum_id = F

and paging_id = v_record_num - 2309 + 1;

update article

set paging_id = paging_id - 1

where forum_id = F

and paging_id > v_record_num - 2309 + 1;

也就是说在删除文章的同时就进行了空洞压缩，这样可以做到不间断运行。

注意，在插入一条新记录时，paging_id不能使用序列对象产生，而是应该使用max获取最大值插入，以保证压缩后依然能够连续。

很明显，如果一次删除时要移动的记录数比较多，那么每次删除的开销比较大，并且在删除时会锁住表，短暂的影响到新文章的发表。

读者可能对上述表结构产生了一些疑问：为何seq_id还有必要存在，有paging_id不是已经足够了吗？没错，就本例而言，完全可以删除seq_id，但是接下来我们马上可以发现，随着功能的进一步加强，seq_id必须存在。

3.5.3 主题模式浏览

有时，因为恢复的文章实在太多，我们想找到初始发表的文章，然后查阅所有相关的恢复文章。在论坛上的表现就是一开始看到的文章全是所谓的主题文章，选择一篇进去之后可以看到相关的所有回复文章。我们把这种模式叫做“主题模式”。

为了支持主题模式，必须对前面的表结构作一定的增强，至少得增加一个字段表示当前的文章的回复了哪篇文章。由于可以对回复文章进一步回复，因此会造成一个树型结构。

create table article

(

forum_id number(4) not null, -- 论坛编号

seq_id number(12) not null, -- 序列号

paging_id number(12) not null, -- 分页用编号

title varchar2(128) not null, -- 文章标题

author varchar2(32) not null, -- 文章作者

publish_time date not null, -- 发表时间

root_id number(12) not null, -- 原始文章

related_id number(12) not null, -- 关联文章

content clob not null -- 文章内容

);

create unique index idx_article on article(forum_id, seq_id);

create unique index idx_article_paging on article(forum_id, paging_id);

create index idx_article_root on article(forum_id, root_id);

在上面的表结构中，增加了两个字段：root_id表示回复文章的最早的主题文章，如果为0则表示本身就是主题文章；而related_id表示直接回复的那篇文章。

对于Oracle开发能力较强的读者来说，可能root_id不是必须的，因为可以使用connect by之类的查询语法来获取树型数据。但是对于MSSQL开发人员来说，困难就要大一些。因此我宁愿选择多一个字段来存放根节点，反正现在的存储空间很便宜。

seq_id依旧表示所有文章的压缩后的序列号，因此按照时间进行分页浏览的功能与上一小节一样，也可以进行文章删除。但是读者可能发现现在的表结构中并没有一个字段是专门为主题文章设立的分页编号。这里我为了简化说明，作了一个假设，认为主题文章的数量比起所有文章数量要少的多，干脆就不用专门的字段来加速查询了。读者可以自己试者添加这样一个字段root_paging_id。

这里的字段root_id和related_id应该指向seq_id，而不是paging_id，因为后者可能由于文章删除而进行压缩。这就是上节中提到的要保留seq_id的原因。另外，为了减少复杂性，这里没有使用外键约束。

现在，如果读者使用主题模式浏览，欲查看第P页的内容，每页显示N行，那么可以这样写代码：

select *

from (select rownum as ord, t.*

from (select *

from article

where root_id = 0

order by seq_id desc) t)

where ord between (P-1)*N and P*N-1;

当用户选择了某一篇主题A后，我们就可以按照这条记录选择到所有的回复记录：

select * from article

where root_id = A

order by seq_id;

如果有特殊需要，也许对回复的所有贴子还要进行分页显示。但是通常使用纯粹的语句分页即可满足要求。

4. MSSQL中如何取第m行到n行数据

4.1 样表

　　首先，按照MSSQL的格式来创建表、索引和数据。

　　本节语句默认在查询分析器中执行。

create table fsjour

(

serial_no numeric(12) NOT NULL, /*流水号 */

branch_no numeric(8) NOT NULL, /*营业部号*/

fund_account numeric(12) NOT NULL, /*资金帐号*/

occur_balance numeric(19,2) NOT NULL, /*发生金额*/

stock_code char(6) NOT NULL, /*股票代码*/

status_code char(1) NOT NULL, /*流水状态*/

constraint pk_fsjour primary key(serial_no)

)

create index idx_fund_account on fsjour(fund_account)

create index idx_stock_code on fsjour(stock_code)

insert into fsjour values(1, 1, 1000881, 12000, '600345', '0')

insert into fsjour values(2, 1, 1007049, 32056, '600740', '0')

insert into fsjour values(3, 1, 1100032, 1245, '600101', '0')

insert into fsjour values(9, 3, 1090801, 93001, '600900', '0')

insert into fsjour values(5, 1, 1045088, 83000, '600371', '0')

insert into fsjour values(4, 1, 1279602, 23466, '600865', '0')

insert into fsjour values(6, 4, 1001003, 38441, '600267', '0')

insert into fsjour values(7, 1, 1003451, 9870, '600541', '0')

insert into fsjour values(8, 1, 1098064, 34844, '600638', '0')

insert into fsjour values(16, 1, 1098064, 34844, '600638', '0')

insert into fsjour values(19, 1, 1000881, 12600, '600345', '0')

insert into fsjour values(10, 1, 1007049, 32006, '600840', '0')

insert into fsjour values(11, 1, 1100032, 12459, '600501', '0')

insert into fsjour values(13, 2, 1090801, 43001, '600903', '0')

insert into fsjour values(12, 1, 1045088, 23100, '000717', '0')

insert into fsjour values(14, 1, 1279602, 50961, '000813', '0')

insert into fsjour values(15, 3, 1001003, 67343, '600834', '0')

insert into fsjour values(18, 1, 1003451, 98701, '600002', '0')

insert into fsjour values(17, 2, 1098064, 9034, '600098', '0')

insert into fsjour values(20, 1, 1098064, 43439, '600718', '0')

insert into fsjour values(21, 1, 1098064, 93483, '600438', '0')

4.2 取前n行

　　T-SQL中可以使用SET ROWCOUNT或者TOP方法来获取前n行数据。其中TOP对单个语句有效，而SET ROWCOUNT则一直生效到下一个SET ROWCOUNT为止。

4.2.1 使用TOP

　　假如我们现在要查询前10名的发生金额，并且按照从大到小排列。那么可以使用以下语句：

select top 10 * from fsjour order by occur_balance desc

　　返回结果为：

serial_no branch_no fund_account occur_balance stock_code status_code

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

9 3 1090801 93001.00 600900 0

5 1 1045088 83000.00 600371 0

14 1 1279602 50961.00 000813 0

13 2 1090801 43001.00 600903 0

6 4 1001003 38441.00 600267 0

8 1 1098064 34844.00 600638 0

16 1 1098064 34844.00 600638 0

2 1 1007049 32056.00 600740 0

10 1 1007049 32006.00 600840 0

4 1 1279602 23466.00 600865 0

(10 row(s) affected)

4.2.2 使用SET ROWCOUNT

　　使用SET ROWCOUNT可以影响之后的所有语句。因此必须注意在不需要的时候将其关闭：

SET ROWCOUNT 0

　　上面的语句表示后续语句不再受影响，即选出全部记录。

　　假如我们现在要查询前10名的发生金额，并且按照从大到小排列。那么可以使用以下语句：

set rowcount 10

select * from fsjour order by occur_balance desc

　　无论是使用哪种方法，由于MSSQL缺少行号支持，我们都无法直接获取排名。

4.2.3 分组取前n行

在2.6.3节中介绍了Oracle的支持分组排名的增强功能，但是在MSSQL就没有这个增强语法了。那么如何在MSSQL中进行分组排名呢？例如，希望获取fsjour表中每个营业部发生金额前2名的流水。这可以通过SQL技巧来实现，当然结果没有Oracle的增强功能那么完美，性能也要差一点。

select branch_no, occur_balance, fund_account, stock_code

from fsjour a

where ((select count(*) from fsjour b

where a.branch_no = b.branch_no

and b.occur_balance >= a.occur_balance) <= 2)

order by branch_no, occur_balance desc

这个语句在Oracle中也是可以用的。执行结果为：

branch_no occur_balance fund_account stock_code

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

1 98701.00 1003451 600002

1 93483.00 1098064 600438

2 43001.00 1090801 600903

2 9034.00 1098064 600098

3 93001.00 1090801 600900

3 67343.00 1001003 600834

4 38441.00 1001003 600267

(7 row(s) affected)

这里采用的技巧是对于需要排名的列occur_balance，将表对自身进行连接，然后在同一个列上进行比较：所谓前2名的记录满足条件为大于等于它的记录不超过2条。

这种方式的一个显然缺点是如果有几条记录的数据相同，可能会导致结果不完全正确。

如果对上面的语句不能很好的理解，那么可以改写一下：

select branch_no, occur_balance, fund_account, stock_code

from fsjour a

where (serial_no in (select top 2 serial_no from fsjour b

where a.branch_no = b.branch_no

order by occur_balance desc))

order by branch_no, occur_balance

结果也是一样的，不过现在使用了IN，语义显得更加明确一点。

4.3 取m行到n行

　　在MSSQL中，当m大于1时获取结果集合是比较复杂的，并没有很好的方法。而在实际应用中，这种结果集合没有多少实际意义。如果要实现翻页应该进行更好的设计，而不是硬写语句实现。

　　例如，我们要查询按照发生金额从大到小排列的第6到10名，可以使用以下语句。该语句使用了三层子查询并进行了三次排序，因此效率和可读性均很差。一个优化的建议是使用临时表。不过，本人建议在设计时尽量避免使用这样的语句。

select tt.*

from (select top 5 t.*

from (select top 10 *

from fsjour

order by occur_balance desc) t

order by occur_balance) tt

order by occur_balance desc

　　该语句的执行结果是：

serial_no branch_no fund_account occur_balance stock_code status_code

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

8 1 1098064 34844.00 600638 0

16 1 1098064 34844.00 600638 0

2 1 1007049 32056.00 600740 0

10 1 1007049 32006.00 600840 0

4 1 1279602 23466.00 600865 0

(5 row(s) affected)

4.3 按索引检索

　　MSSQL在使用索引进行查询时，会产生索引顺序的记录集合。这可以避免对结果进行排序。必须小心编写语句的条件，以便其能够使用正确的索引。特别是在条件中含有多个列时，MSSQL可能会使用其他索引进行查询，导致结果错误。此时，可以考虑使用指定索引的方法。

　　下面的这个语句：

select top 10 * from fsjour where stock_code > '000000'

　　会返回前10种证券代码记录，并且结果是按照代码进行排序的。结果是：

serial_no branch_no fund_account occur_balance stock_code status_code

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

12 1 1045088 23100.00 000717 0

14 1 1279602 50961.00 000813 0

18 1 1003451 98701.00 600002 0

17 2 1098064 9034.00 600098 0

3 1 1100032 1245.00 600101 0

6 4 1001003 38441.00 600267 0

1 1 1000881 12000.00 600345 0

19 1 1000881 12600.00 600345 0

5 1 1045088 83000.00 600371 0

21 1 1098064 93483.00 600438 0

(10 row(s) affected)

　　但是下面的语句，则使用了不同的索引进行查询：

select top 10 * from fsjour

where stock_code > '000000'

and fund_account > 0

　　其结果是：

serial_no branch_no fund_account occur_balance stock_code status_code

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

1 1 1000881 12000.00 600345 0

19 1 1000881 12600.00 600345 0

6 4 1001003 38441.00 600267 0

15 3 1001003 67343.00 600834 0

7 1 1003451 9870.00 600541 0

18 1 1003451 98701.00 600002 0

2 1 1007049 32056.00 600740 0

10 1 1007049 32006.00 600840 0

5 1 1045088 83000.00 600371 0

12 1 1045088 23100.00 000717 0

(10 row(s) affected)

　　可以看出，结果是完全不对的。

　　此时，必须加上order by stock_code才能保证结果的正确。或者使用指定索引的方法，用以下语句：

select top 10 * from fsjour (INDEX=idx_stock_code)

where stock_code > '000000'

and fund_account > 0

　　采用何种方式根据开发人员的喜好而定。本人偏好使用索引提示，因为避免使用排序总是一件提高效率的事情。

5. MSSQL的分页显示

5.1 根据指定行号取当前页数据

　　例如，如果一页最多显示5行数据，那么第三页就是显示第11行到第15行数据。这相当于4.3节中所描述的那样。

　　这种方式的缺点是使用了表扫描，而且还要排序，效率低下。如果表中有大量数据，这种方式根本不可取。

5.2 根据索引依次向后取n行

　　提高查询效率的基本方法是使用索引。例如，我们希望按照证券代码次序来翻页，那么，可以使用以下语句：

select top 5 * from fsjour

where stock_code > '' or stock_code= '' and serial_no > 0

order by stock_code, serial_no

　　其中的前两个?表示前一页的结束记录的证券代码值，第三个?表示前一页的结束记录的流水号值。

　　在我们的例子中，第一页可以使用’’，第二页则使用第一页的最后一个记录的值，即5。让我们来看第一页的输出：

serial_no branch_no fund_account occur_balance stock_code status_code

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

12 1 1045088 23100.00 000717 0

14 1 1279602 50961.00 000813 0

18 1 1003451 98701.00 600002 0

17 2 1098064 9034.00 600098 0

3 1 1100032 1245.00 600101 0

(5 row(s) affected)

　　其最后一行的证券代码值是600101，流水号是3。将其作为参数送给第二页的语句。

　　读者可能会问为何要在条件中引入serial_no。这是因为stock_code并不唯一，如果仅将语句写成：

select top 5 * from fsjour

where stock_code > ?

order by stock_code

　　那么如果某一页的最后一条记录后面有一条与之证券代码相同的记录，该记录就可能丢失。

　　如果将语句写成：

select top 5 * from fsjour

where stock_code >= ?

order by stock_code

　　那么最后一条记录将在下一页上重复出现，并且如果相同证券代码的记录数量超过一页的话，有可能会无法向前滚动，只在某一页不断重复。

5.3 根据索引依次向前取n行

　　首先，如果有可能的话，尽量将已经读过的数据缓存在本地，这样就不用在回滚页面时与数据哭交互，减少开销。

　　如果已读数据将会发生变化，那么，不可避免要向数据库发起查询。很不幸，MSSQL没有很好的机制来支持这种行为，必须使用一定技巧来实现。

　　继续考虑上小节中的例子，如果我们知道当前页的第一条记录是：

serial_no branch_no fund_account occur_balance stock_code status_code

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

11 1 1100032 12459.00 600501 0

　　那么，其前一页的查询语句可以为：

select *

from (select top 5 * from fsjour

where stock_code < ? or stock_code= ? and serial_no < ?

order by stock_code desc, serial_no desc) t

order by stock_code, serial_no

　　该语句中的子查询首先倒排序，这样可以使所需要的一页数据出现在最前面，然后再将这一页数据用TOP取出后进行正向排序。

　　由于排序时涉及两个列，所以倒排序时要分别加上DESC。读者可以仔细体会其中的道理。

　　用’600501’和11替换上面的?，可得到结果为：

serial_no branch_no fund_account occur_balance stock_code status_code

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

6 4 1001003 38441.00 600267 0

1 1 1000881 12000.00 600345 0

19 1 1000881 12600.00 600345 0

5 1 1045088 83000.00 600371 0

21 1 1098064 93483.00 600438 0

(5 row(s) affected)

6. 记录的存放次序

　　在本文中，我还想与读者讨论的一个话题是表中记录存放的顺序。所谓物理顺序，是指数据在磁盘上存放的顺序，对应的，逻辑顺序是指查询语句返回的记录的次序。在前面的描述中，我们已经描述了逻辑顺序的相关内容。

6.1 MSSQL的聚集索引

　　在MSSQL中，在创建索引时使用关键字CLUSTERED，将创建所谓的聚集索引。该索引将影响表中记录的物理顺序，即这些记录将按照索引的顺序在磁盘上依次排列。如果一个表上尚未创建聚集索引，创建PRIMARY KEY将同时创建出聚集索引。显而易见，一个表上只能有一个聚集索引。

聚集索引对范围搜索特别有效，因为数据已经排序好，所以免去了排序过程。并且在磁盘读数据时只需要读取连续的几个物理数据快就可以了，大大提高了读取速度。但是必须指出，将数据按照物理次序排序存放，需要很大的代价。不利之处主要有两点：一是新增加一条记录时，必须在已有的数据中找出一个空槽，有时，这需要移动数据。对于流水号形式的记录，一般可以避免这个问题，因为流水不断增大，新增加的数据只是添加在最后而已，不需要移动前面的数据。第二个问题是所谓的磁盘“写热点”问题。由于数据是排好次序的，因此当连续插入多条数据时，要求磁盘读写头始终在一个或者连续的几个物理数据块上操作。如果同时又有其他的表上的读写操作，那么磁盘的随机读写功能就会受到影响。有时，会看到磁头来回不停移动，或者某些请求的响应时间变的很长。

现在来看一下聚集索引的效果。还是利用4.1节中的fsjour表，但是我们在创建主键时将其定义为聚集索引。

drop table fsjour

create table fsjour

(

serial_no numeric(12) NOT NULL, /*流水号 */

branch_no numeric(8) NOT NULL, /*营业部号*/

fund_account numeric(12) NOT NULL, /*资金帐号*/

occur_balance numeric(19,2) NOT NULL, /*发生金额*/

stock_code char(6) NOT NULL, /*股票代码*/

status_code char(1) NOT NULL /*流水状态*/

)

create unique clustered index pk_fsjour on fsjour(serial_no)