問:我的一個表中包含了名為IdValue的單列主鍵。對於給定的IdValue值,我希望找到緊鄰目標值之前和之後的表行(假定結果按IdValue排序)。怎樣才能不使用游標而通過一個基於集合的方法得到需要的結果?
答:Transact-SQL是一個基於集合的語言,使用它在結果集中定位特定的行並非一件易事。但是,服務器端ANSI Transact-SQL游標的性能遠遠不如基於集合的解決方案,因此,學習解決問題的多種技術非常重要,尤其在面臨上述問題時。
以Northwind數據庫中的Orders表為例。我們可以這樣重述該問題:怎樣才能在Orders表中找到緊鄰特定行之前和之後的行而不使用游標?假設我們按照OrderId列對結果集排序。
創造性地使用SQL Server的MIN()、MAX()和 TOP功能可以幫助您解決諸如此類的結果集定位問題。程序清單1和2給出了兩個相似的、僅有細微差別的解決方法。程序清單1提供了一個常見的解決方案,因為它運用了@TargetOrder的MIN()和MAX()終點。但在某些情況下,展示TOP的靈活性也很有用。請注意,在以參數方式提供Orders表的最小OrderId(10248)時,程序清單2中的查詢將返回空集,因為該查詢假定在@TargetOrder行前總有一個行存在。
一般說來,生成結果集的方法不止一種,其中某一方法通常比其他的更高效。當您對這兩個例子評估SHOWPLAN和SET STATISTICS IO信息時,您會發現運用了TOP語句的程序清單2的效率略微高於程序清單1。差別很細微的原因在於樣本數據集很小,但在存在多種查詢方法的情況下,測試不同方法的性能非常重要。
—Brian Moran
程序清單1:使用包含OR關鍵字的MIN() and MAX()函數查找目標行
DECLARE @TargetOrder int
SET @TargetOrder=10330
FROM Orders
WHERE
OR OrderId=(SELECT MAX(OrderId)
FROM orders WHERE OrderId < @TargetOrder)
OR OrderId = (SELECT MIN(OrderId)
FROM orders WHERE OrderId > @TargetOrder)
程序清單2:使用TOP關鍵字查找目標行
SELECT
TOP 3
*
FROM orders
WHERE OrderId >=(SELECT MAX(OrderId) FROM orders
WHERE OrderId < @TargetOrder)
ORDER BY
OrderId