首页 | 社区 | 博客 | 招聘 | 文章 | 新闻 | 下载 | 项目交易 | 网友作品 | 读书 | ACM题库 | 源码
亲,您未登录哦! 登录 | 注册

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

(加入日期: 2002-01-14 )

打印文章】【字体:

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

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

本栏文章均来自于互联网,版权归原作者和各发布网站所有,本站收集这些文章仅供学习参考之用。任何人都不能将这些文章用于商业或者其他目的。( Pfan.cn )

编程爱好者论坛

本栏最新文章