数据库查询结果的动态排序

在公共新闻组中，一个经常出现的问题是“怎样才能根据传递给存储过程的参数返回一个排序的输出？”。在一些高水平专家的帮助之下，我整理出了这个问题的几种解决方案。

一、用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表达式和动态执行能力的各种方案。

原文转自：http://www.ltesting.net