Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->select * from table1 limit 1, 20
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
上面的SQL表示從table1中查出記錄,並返回從第2條開始的20條記錄(第1條記錄從0開始)。 對於其他的資料庫,恐怕就沒MySQL那麼容易查詢出記錄段了。在SQL Server2005中也提供了類似MySQL的處理方法(可以使用ROW_NUMBER()函式來實現這個功能),SQL語句如下:
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
雖然上面的SQL語句雖然也可以實現和MySQL一樣的功能,但卻比MySQL的limit複雜一些。 如果在資料庫中提供了實現Web分頁的機制,就算複雜一些,也是可以解決的。但有效資料庫可能並未提供這種機制。這就得使用更復雜的方法來實現Web分頁,如在SQL
Server2000中未提供ROW_NUMBER()函式,就有很多開發人員通過編寫分頁的儲存過程來處理。這樣做既複雜,又不通用。假設要移植到Oracle上,還得費一番功夫。 在本文給出一種直接使用Web中的Session物件來方式來實現分頁的功能,Session是在Web系統中儲存當前分話資料的。我們可以想象。分頁的難點在哪裡,就象MySQL中的limit語句一樣,只需要有兩個值:起使記錄數和要獲得的記錄總數就可以了。要獲得的記錄總數這個我們很容易知道,一般就是分一頁的記錄數。但是起使記錄數卻很難獲得。 如果使用自增鍵當然可以,但這要建立在表只增不刪,而且id從1或一個已知的起始位置開始的情況。如果刪除了表中的一些資料,自增鍵就不再是從1到n,依次遞增了。也就是中間可能有空檔。如自增鍵從20至100,中間可能只有10條記錄。因此,單純使用自增鍵並不能很好地解決分頁問題。 但卻可以將Session和自增鍵組合來解決分頁問題。大家可以設想,在使用者第一次查詢時,如select
* from table1 where field1 like '%abc%',這時將記錄全部查出。假設每頁顯示50條記錄,這時可以從頭開始取出50條記錄。這不會有任何問題。然後,當使用者要檢視第2頁時,最普通的做是再執行一次上面的SQL語句,然後從第51第記錄開始,再取出50條記錄。如果這樣做,將大大浪費伺服器的資源。 為了解決這個問題,可以在每一次執行完上面的SQL語句後,除了取出前50條記錄外,再通過記錄的定位,將其他頁面的起始id值儲存在Session中(可以放在List物件中)。然後在使用者要檢視第2頁或後面的頁時,直接從Session中取出該頁起始id的值,如果使用的是SQL Servlet資料庫,可以使用top n,其中n表示每頁記錄數,來查詢當前頁的記錄。 先拿Java為例來說明一下。下面的程式碼在Session中記錄了第一頁到最後一頁的起始id:
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
從上面的程式碼可以看出,使用ResultSet的absolute來定位記錄,並取出當前記錄的id值(一個自增欄位),並將其儲存在Session中。 假設共查詢出500條記錄,那麼Session中儲存的id值有可能是下面的樣子:
1 51 123 179 229 290 367 567 699 從上面的id值可以看出,中間有斷檔。但這9個id值之間的記錄數都是50個。如下面的SQL語句將查詢出50個記錄: 假設使用者要檢視第3頁的話,就會取出123和179,並將其加入select 語句的where條件,類似上面的SQL語句。這樣使用者除了第一次查詢外,檢視其他頁都會只返回當前頁面的記錄了。 上面的方法還有一些問題,如當第一次返回的記錄很多的話,使用absolute方法進行迴圈所有的記錄可能有些慢,那可以在程式中做個約定,只迴圈41次,也就是儲存前40頁的記錄,當使用者要檢視第41頁的話,再取出第40頁的開始記錄的id值,將再次查詢從該id值往後的所有記錄,再記錄40頁的id值,也就是這時已經有80頁的id記錄被儲存在Session中的。以此類推, 當然,這種方法也不可避免地遇到刪除記錄的情況,如果使用者正在檢視頁面,這時某一頁的記錄被刪除了,當使用者再次要檢視這頁時,根據Session中儲存的id區間,就會得到少於50的記錄。在這種情況下,如果使用的是SQL Servlet,就好辦一些,可以在where條件中只加id的上限,不加下限,然後使用top關鍵字來限制查詢出的記錄數,SQL語句如下: 如果使用的是其他資料庫,沒有型別top的關鍵字,可以在查詢時多加一個區間,如使用者要查詢第2頁的資料,可以將第2頁和第3頁的都查出來,這樣一般就可以獲得超過50條的記錄。但如果記錄數還不夠(這個表的記錄被刪除的太多了),筆者建議重新查詢所有的記錄,重新更新一下Session物件中的id值。 總之,本演算法就是在第一次查詢時預先將後面頁面的起始記錄的id值事先儲存起來,然後等待以後檢視其他頁面時使用。如果這時某個頁面的記錄被刪除(如果當前頁面記錄數不足頁面記錄總數,被示為有記錄刪除),可以重新更新一下Session中的id值,然後根據新的id值再查一遍。但要注意的是這個id值最好使用資料庫的自增型欄位(一般的資料庫,甚至桌面資料庫都會有自增型欄位型別)。為了儘量避免總更新Session中的id值,可以在查詢一個頁面時查詢出兩個頁面的記錄,這樣在一般情況下,會保證記錄數超過頁面記錄總數。但這樣做一個缺點,就是可能兩個相鄰頁面的記錄有一定的重複。不過並沒有太大影響。我們在網上看某些論壇的貼子時,有時可能也會發現兩個相鄰頁面的記錄有重複。 本分頁方法適合於所有的資料庫,無論是網路資料庫(Oracle、SQL Servlet、DB2等),以及桌面資料庫(access、paradox、pdf等)。並且不需要在資料庫中建立額外的資源,如儲存過程等。(當然,每個表需要有一個自增型別欄位,這一點很關鍵)。 哪位讀者有更好,更通用的分頁方法(最好不要在資料庫中建立象儲存過程一樣的資源,儘量不要使用與資料庫相關的語句,如SQL Server中的top),請跟貼。
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->With t AS
(
SELECT contactid, namestyle, lastname,
ROW_NUMBER() over(order by namestyle) as RowNumber
FROM Person.Contact
)
select * from t
Where RowNumber Between 20 and 30
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->// rs為記錄集,其他語言的操作類似
ResultSet rs = stmt.executeQuery("select * from table1 where field1 like '%abc%'");
int n = 1;
while(rs.absolute(n))
{
int id = rs.getInt(id)
// 將id儲存在Session中
n += 50;
}
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->select * from table1 where field1 like '%abc%' and (id >= 290 and id < 367)
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->select top 50 * from table1 where field1 like '%abc%' and id >= 290