数据库查询结果的动态排序
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表达式和动态执行能力的各种方案。
