分頁的SQL語法問題

2008-04-01

42700
0
SQL
2011-01-13

摘要:分頁的SQL語法問題

之前在網路上看到下面的分頁SQL語法,

Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers where CustomerID Not in
(Select top 40 CustomerID from Customers order by Country,CustomerID)
Order by Country,CustomerID

這個語法只要在google查詢分頁,
大部分在討論如何用SQL分頁的文章中都會提到,
而從這個查詢語法可以導出如下的公式

SELECT TOP 每頁筆數 * FROM table
WHERE (field NOT IN
(SELECT TOP (每頁幾筆*(頁數-1)) field FROM table ORDER BY field)
)ORDER BY field

而之前我在試用這個語法時發現where的條件多了後,效能上有很大的問題,
如果以SQL中的Northwind資料結構為例,
假如查詢的條件是每頁10筆,而 country 中有 "UK" 或是 CompanyName 中有 "UK"的話,
則查詢式應該是
第一頁

Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers
where (Country like '%UK%' or CompanyName like '%UK%')
Order by Country,CustomerID

因為第一頁顯示前10筆,所以直接用top 10 就可以,不用在加not in

第二頁

Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers
where (Country like '%UK%' or CompanyName like '%UK%') and CustomerID Not in
(Select top 10 CustomerID from Customers where (Country like '%UK%' or CompanyName like '%UK%') order by Country,CustomerID)
Order by Country,CustomerID

第一頁,通常是沒有問題的,
但是到了第二頁,
如果將這個語法丟到SQL Server Enterprise Manager中的建立檢視的功能中去檢查的話,
你會發現檢查完後enterprise將語法改成如下

SELECT TOP 10 CustomerID, CompanyName, ContactName, Country
FROM dbo.Customers
WHERE (Country LIKE '%UK%') AND (CustomerID NOT IN
(SELECT TOP 10 CustomerID
FROM Customers
WHERE (Country LIKE '%UK%' OR
CompanyName LIKE '%UK%')
ORDER BY Country, CustomerID)) OR
(CustomerID NOT IN
(SELECT TOP 10 CustomerID
FROM Customers
WHERE (Country LIKE '%UK%' OR
CompanyName LIKE '%UK%')
ORDER BY Country, CustomerID)) AND (CompanyName LIKE '%UK%')
ORDER BY Country, CustomerID

而這時我們就會發現,SQL對這樣的語法似乎不是像我們想像的那樣執行,
如果當資料筆數多了,而這個語法的執行時間就會久的嚇人...

只是...不知道是我我語法本來就有問題還是怎樣,
因為在網路上似乎沒有看過有人對這樣的語法提出任何質疑...

回首頁