摘要:分頁的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
(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 (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
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
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
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對這樣的語法似乎不是像我們想像的那樣執行,
如果當資料筆數多了,而這個語法的執行時間就會久的嚇人...
只是...不知道是我我語法本來就有問題還是怎樣,
因為在網路上似乎沒有看過有人對這樣的語法提出任何質疑...