數據庫查詢結果的動態排序
2024-07-21 02:06:06
供稿：網友
在公共新聞組中，一個經常出現的問題是“怎樣才能根據傳遞給存儲過程的參數返回一個排序的輸出？”。在一些高水平專家的幫助之下，我整理出了這個問題的幾種解決方案。
一、用if...else執行預先編寫好的查詢
對于大多數人來說，首先想到的做法也許是：通過if...else語句，執行幾個預先編寫好的查詢中的一個。例如，假設要從northwind數據庫查詢得到一個貨主（shipper）的排序列表，發出調用的代碼以存儲過程參數的形式指定一個列，存儲過程根據這個列排序輸出結果。listing 1顯示了這種存儲過程的一個可能的實現（getsortedshippers存儲過程）。
【listing 1: 用if...else執行多個預先編寫好的查詢中的一個】
create proc getsortedshippers
@ordseq as int
as
if @ordseq = 1
select * from shippers order by shipperid
else if @ordseq = 2
select * from shippers order by companyname
else if @ordseq = 3
select * from shippers order by phone
這種方法的優點是代碼很簡單、很容易理解，sql server的查詢優化器能夠為每一個select查詢創建一個查詢優化計劃，確保代碼具有最優的性能。這種方法最主要的缺點是，如果查詢的要求發生了改變，你必須修改多個獨立的select查詢——在這里是三個。
二、用列名字作為參數
另外一個選擇是讓查詢以參數的形式接收一個列名字。listing 2顯示了修改后的getsortedshippers存儲過程。case表達式根據接收到的參數，確定sql server在order by子句中使用哪一個列值。注意，order by子句中的表達式并未在select清單中出現。在ansi sql-92標準中，order by子句中不允許出現沒有在select清單中指定的表達式，但ansi sql-99標準允許。sql server一直允許這種用法。
【listing 2：用列名字作為參數，第一次嘗試】
create proc getsortedshippers
@colname as sysname
as
select *
from shippers
order by
case @colname
when 'shipperid' then shipperid
when 'companyname' then companyname
when 'phone' then phone
else null
end
現在，我們來試一下新的存儲過程，以參數的形式指定shipperid列：
exec getsortedshippers 'shipperid'
此時一切正常。但是，當我們視圖把companyname列作為參數調用存儲過程時，它不再有效：
exec getsortedshippers 'companyname'
仔細看一下錯誤信息：
server: msg 245, level 16, state 1, procedure getsortedshippers, line 5
syntax error converting the nvarchar value 'speedy
express' to a column of data type int.
它顯示出，sql server試圖把“speedy express”（nvarchar數據類型）轉換成一個整數值——當然，這個操作是不可能成功的。出現錯誤的原因在于，按照“數據類型優先級”規則，case表示式中最高優先級的數據類型決定了表達式返回值的數據類型。“數據類型優先級”規則可以在sql server books online（bol）找到，它規定了int數據類型的優先級要比nvarchar數據類型高。前面的代碼要求sql server按照companyname排序輸出，companyname是nvarchar數據類型。這個case表達式的返回值可能是shipperid（int類型），可能是companyname（nvarchar類型），或phone（nvarchar類型）。由于int類型具有較高的優先級，因此case表達式返回值的數據類型應該是int。
為了避免出現這種轉換錯誤，我們可以嘗試把shipperid轉換成varchar數據類型。采用這種方法之后，nvarchar將作為最高優先級的數據類型被返回。listing 3顯示了修改后的getsortedshippers存儲過程。
【listing 3：用列名字作為參數，第二次嘗試】
alter proc getsortedshippers
@colname as sysname
as
select *
from shippers
order by
case @colname
when 'shipperid'
then cast(shipperid as varchar(11))
when 'companyname'
then companyname
when 'phone'
then phone
else null
end
現在，假設我們再把三個列名字中的任意一個作為參數調用存儲過程，輸出結果看起來正確。看起來就象指定的列正確地為查詢輸出提供了排序標準。但這個表只有三個貨主，它們的id分別是1、2、3。假設我們把更多的貨主加入到表，如listing 4所示（shipperid列有identity屬性，sql server自動為該列生成值）。
【listing 4：向shippers表插入一些記錄】
insert into shippers values('shipper4', '(111) 222-9999')
insert into shippers values('shipper5', '(111) 222-8888')
insert into shippers values('shipper6', '(111) 222-7777')
insert into shippers values('shipper7', '(111) 222-6666')
insert into shippers values('shipper8', '(111) 222-5555')
insert into shippers values('shipper9', '(111) 222-4444')
insert into shippers values('shipper10', '(111) 222-3333')
現在調用存儲過程，指定shipperid作為排序列：
exec getsortedshippers 'shipperid'
表一顯示了存儲過程的輸出。shipperid等于10的記錄位置錯誤，因為這個存儲過程的排序輸出是字符排序，而不是整數排序。按照字符排序時，10排列在2的前面，因為10的開始字符是1。
表一：記錄排序錯誤的查詢結果
shipperid companyname phone
1 speedy express (503) 555-9831
10 shipper10 (111) 222-3333
2 united package (503) 555-3199
3 federal shipping (503) 555-9931
4 shipper4 (111) 222-9999
5 shipper5 (111) 222-8888
6 shipper6 (111) 222-7777
7 shipper7 (111) 222-6666
8 shipper8 (111) 222-5555
9 shipper9 (111) 222-4444
為了解決這個問題，我們可以用前置的0補足shipperid值，使得shipperid值都有同樣的長度。按照這種方法，基于字符的排序具有和整數排序同樣的輸出結果。修改后的存儲過程如listing 5所示。十個0被置于shipperid的絕對值之前，而在結果中，代碼只是使用最右邊的10個字符。sign函數確定在正數的前面加上加號（+）前綴，還是在負數的前面加上負號（-）前綴。按照這種方法，輸出結果總是有11個字符，包含一個“+”或“-”字符、前導的字符0以及shipperid的絕對值。
【listing 5：用列名字作為參數，第三次嘗試】
alter proc getsortedshippers
@colname as sysname
as
select *
from shippers
order by
case @colname
when 'shipperid' then case sign(shipperid)
when -1 then '-'
when 0 then '+'
when 1 then '+'
else null
end +
right(replicate('0', 10) +
cast(abs(shipperid) as varchar(10)), 10)
when 'companyname' then companyname
when 'phone' then phone
else null
end
如果shipperid的值都是正數，加上符號前綴就沒有必要，但為了讓方案適用于盡可能多的范圍，本例加上了符號前綴。排序時“-”在“+”的前面，所以它可以用于正、負數混雜排序的情況。
現在，如果我們用任意三個列名字之一作為參數調用存儲過程，存儲過程都能夠正確地返回結果。richard romley提出了一種巧妙的處理方法，如listing 6所示。它不再要求我們搞清楚可能涉及的列數據類型。這種方法把order by子句分成三個獨立的case表達式，每一個表達式處理一個不同的列，避免了由于case只返回一種特定數據類型的能力而導致的問題。
【listing 6：用列名字作為參數，romley提出的方法】
alter proc getsortedshippers
@colname as sysname
as
select *
from shippers
order by
case @colname when 'shipperid'
then shipperid else null end,
case @colname when 'companyname'
then companyname else null end,
case @colname when 'phone'
then phone else null end
按照這種方法編寫代碼，sql server能夠為每一個case表達式返回恰當的數據類型，而且無需進行數據類型轉換。但應該注意的是，只有當指定的列不需要進行計算時，索引才能夠優化排序操作。
三、用列號作為參數
就象第一個方案所顯示地那樣，你也許更喜歡用列的編號作為參數，而不是使用列的名字（列的編號即一個代表你想要作為排序依據的列的數字）。這種方法的基本思想與使用列名字作為參數的思想一樣：case表達式根據指定的列號確定使用哪一個列進行排序。listing 7顯示了修改后的getsortedshippers存儲過程。
【listing 7：用列號作為參數】
alter proc getsortedshippers
@colnumber as int
as
select *
from shippers
order by
case @colnumber
when 1 then case sign(shipperid)
when -1 then '-'
when 0 then '+'
when 1 then '+'
else null
end +
right(replicate('0', 10) +
cast(abs(shipperid) as varchar(10)), 10)
when 2 then companyname
when 3 then phone
else null
end
當然，在這里你也可以使用richard的方法，避免order by子句中列數據類型帶來的問題。如果要根據shipperid排序輸出，你可以按照下面的方式調用修改后的getsortedshippers存儲過程：
exec getsortedshippers 1
四、動態執行
使用動態執行技術，我們能夠更輕松地編寫出getsortedshippers存儲過程。使用這種方法時，我們只需動態地構造出select語句，然后用exec()命令執行這個select語句。假設傳遞給存儲過程的參數是列的名字，存儲過程可以大大縮短：
alter proc getsortedshippers
@colname as sysname
as
exec('select * from shippers order by ' +
@colname)
在sql server 2000和7.0中，你可以用系統存儲過程sp_executesql替代exec()命令。bol說明了使用sp_executesql比使用exec()命令更有利的地方。一般地，如果滿足以下三個條件，你能夠在不授予存儲過程所涉及對象權限的情況下，授予執行存儲過程的權限：首先，只使用data manipulation language（dml）語言（即select，insert，update，delete）；其次，所有被引用的對象都有與存儲過程同樣的所有者；第三，沒有使用動態命令。
上面的存儲過程不能滿足第三個條件。在這種情況下，你必須為所有需要使用存儲過程的用戶和組顯式地授予shippers表的select權限。如果這一點可以接受的話，一切不存在問題。類似地，你可以修改存儲過程，使它接受一個列號參數，如listing 8所示。
【listing 8：用列號作為參數，動態執行（代碼較長的方法）】
alter proc getsortedshippers
@colnumber as int
as
declare @cmd as varchar(8000)
set @cmd = 'select * from shippers order by ' +
case @colnumber
when 1 then 'shipperid'
when 2 then 'companyname'
when 3 then 'phone'
else 'null'
end
exec(@cmd)
注意，當你使用了函數時，你應該在一個變量而不是exec()命令內構造select語句。此時，case表達式動態地確定使用哪一個列。還有一種更簡短的格式，t-sql允許在order by子句中指定select清單中列的位置，如listing 9所示。這種格式遵從了sql-92標準，但ansi sql-99標準不支持這種格式，所以最好不要使用這種格式。
【listing 9：列號作為參數，動態執行（代碼較短的方法）】
alter proc getsortedshippers
@colnumber as int
as
declare @cmd as varchar(8000)
set @cmd = 'select * from shippers order by ' + cast(@colnumber as varchar(4))
exec(@cmd)
五、用戶定義函數
如果你使用的是sql server 2000，想要編寫一個用戶定義的函數（udf），這個用戶定義函數接受列的名字或編號為參數、返回排序的結果集，listing 10顯示了大多數程序員當成第一選擇的方法。
【listing 10：列名字作為參數，使用udf】
create function ufn_getsortedshippers
(
@colname as sysname
)
returns table
as
return
select *
from shippers
order by
case @colname
when 'shipperid' then case sign(shipperid)
when -1 then '-'
when 0 then '+'
when 1 then '+'
else null
end +
right(replicate('0', 10) +
cast(abs(shipperid) as
varchar(10)), 10)
when 'companyname' then companyname
when 'phone' then phone
else null
end
但是，sql server不接受這個函數，它將返回如下錯誤信息：
server: msg 1033, level 15, state 1, procedure ufn_getsortedshippers,
line 24
the order by clause is invalid in views, inline functions, and
subqueries, unless top is also specified.
注意錯誤信息中的“unless”。sql server 2000不允許在視圖、嵌入式udf、子查詢中出現order by子句，因為它們都應該返回一個表，表不能指定行的次序。然而，如果使用了top關鍵詞，order by子句將幫助確定查詢所返回的行。因此，如果指定了top，你還可以同時指定order by。由于在帶有top的udf中允許使用order by子句，你可以使用一個技巧：把“select *”替換成“select top 100 percent *”。這樣，你就能夠成功地構造出一個接受列名字或編號為參數、返回排序結果的函數。
新構造的函數可以按照如下方式調用：
select * from ufn_getsortedshippers('shipperid')
現在，你已經了解了幾種用參數確定查詢輸出中記錄次序的方法。在編寫那些允許用戶指定查詢結果排序標準的列的應用程序時，你可以使用本文介紹的各種技術，用列名字或編號作為參數，構造出使用case表達式和動態執行能力的各種方案。
