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

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

　 　一 用IF ELSE执行预先编写好的查询

对于大多数人来说 首先想到的做法也许是 通过IF ELSE语句 执行几个预先编写好的查询中的一个 例如 假设要从Northwind数据库查询得到一个货主(Shipper)的排序列表 发出调用的代码以存储过程参数的形式指定一个列 存储过程根据这个列排序输出结果 Listing 显示了这种存储过程的一个可能的实现(GetSortedShippers存储过程)

【Listing : 用IF ELSE执行多个预先编写好的查询中的一个】

CREATE PROC GetSortedShippers

@OrdSeq AS int

AS

IF @OrdSeq =

SELECT * FROM Shippers ORDER BY ShipperID

ELSE IF @OrdSeq =

SELECT * FROM Shippers ORDER BY CompanyName

ELSE IF @OrdSeq =

SELECT * FROM Shippers ORDER BY Phone

这种方法的优点是代码很简单 很容易理解 SQL Server的查询优化器能够为每一个SELECT查询创建一个查询优化计划 确保代码具有最优的性能 这种方法最主要的缺点是 如果查询的要求发生了改变 你必须修改多个独立的SELECT查询(在这里是三个)

　 　二 用列名字作为参数

另外一个选择是让查询以参数的形式接收一个列名字 Listing 显示了修改后的GetSortedShippers存储过程 CASE表达式根据接收到的参数 确定SQL Server在ORDER BY子句中使用哪一个列值 注意 ORDER BY子句中的表达式并未在SELECT清单中出现 在ANSI SQL 标准中 ORDER BY子句中不允许出现没有在SELECT清单中指定的表达式 但ANSI SQL 标准允许 SQL Server一直允许这种用法

【Listing 用列名字作为参数 第一次尝试】

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 Level State Procedure GetSortedShippers Line

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 显示了修改后的GetSortedShippers存储过程

【Listing 用列名字作为参数 第二次尝试】

ALTER PROC GetSortedShippers

@ColName AS sysname

AS

SELECT *

FROM Shippers

ORDER BY

CASE @ColName

WHEN ShipperID

THEN CAST(ShipperID AS varchar( ))

WHEN CompanyName

THEN CompanyName

WHEN Phone

THEN Phone

ELSE NULL

END

现在 假设我们再把三个列名字中的任意一个作为参数调用存储过程 输出结果看起来正确 看起来就象指定的列正确地为查询输出提供了排序标准 但这个表只有三个货主 它们的ID分别是 假设我们把更多的货主加入到表 如Listing 所示(ShipperID列有IDENTITY属性 SQL Server自动为该列生成值)

【Listing 向Shippers表插入一些记录】

INSERT INTO Shippers VALUES( Shipper ( ) )

INSERT INTO Shippers VALUES( Shipper ( ) )

INSERT INTO Shippers VALUES( Shipper ( ) )

INSERT INTO Shippers VALUES( Shipper ( ) )

INSERT INTO Shippers VALUES( Shipper ( ) )

INSERT INTO Shippers VALUES( Shipper ( ) )

INSERT INTO Shippers VALUES( Shipper ( ) )

现在调用存储过程 指定ShipperID作为排序列

EXEC GetSortedShippers ShipperID

表一显示了存储过程的输出 ShipperID等于 的记录位置错误 因为这个存储过程的排序输出是字符排序 而不是整数排序 按照字符排序时 排列在 的前面 因为 的开始字符是

表一 记录排序错误的查询结果

ShipperID CompanyName 　Phone

　Speedy Express 　( )

　Shipper 　( )

　United Package 　( )

　Federal Shipping ( )

　Shipper ( )

　Shipper ( )

　Shipper ( )

　Shipper ( )

　Shipper ( )

　Shipper ( )

为了解决这个问题 我们可以用前置的 补足ShipperID值 使得ShipperID值都有同样的长度 按照这种方法 基于字符的排序具有和整数排序同样的输出结果 修改后的存储过程如Listing 所示 十个 被置于ShipperID的绝对值之前 而在结果中 代码只是使用最右边的 个字符 SIGN函数确定在正数的前面加上加号(+)前缀 还是在负数的前面加上负号( )前缀 按照这种方法 输出结果总是有 个字符 包含一个 + 或 字符 前导的字符 以及ShipperID的绝对值

【Listing 用列名字作为参数 第三次尝试】

ALTER PROC GetSortedShippers

@ColName AS sysname

AS

SELECT *

FROM Shippers

ORDER BY

CASE @ColName

WHEN ShipperID THEN CASE SIGN(ShipperID)

WHEN THEN

WHEN THEN +

WHEN THEN +

ELSE NULL

END + RIGHT(REPLICATE( ) + CAST(ABS(ShipperID) AS varchar( )) )

WHEN CompanyName THEN CompanyName

WHEN Phone THEN Phone

ELSE NULL

END

如果ShipperID的值都是正数 加上符号前缀就没有必要 但为了让方案适用于尽可能多的范围 本例加上了符号前缀 排序时 在 + 的前面 所以它可以用于正 负数混杂排序的情况

现在 如果我们用任意三个列名字之一作为参数调用存储过程 存储过程都能够正确地返回结果 Richard Romley提出了一种巧妙的处理方法 如Listing 所示 它不再要求我们搞清楚可能涉及的列数据类型 这种方法把ORDER BY子句分成三个独立的CASE表达式 每一个表达式处理一个不同的列 避免了由于CASE只返回一种特定数据类型的能力而导致的问题

【Listing 用列名字作为参数 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 显示了修改后的GetSortedShippers存储过程

【Listing 用列号作为参数】

ALTER PROC GetSortedShippers

@ColNumber AS int

AS

SELECT *

FROM Shippers

ORDER BY

CASE @ColNumber

WHEN THEN CASE SIGN(ShipperID)

WHEN THEN

WHEN THEN +

WHEN THEN +

ELSE NULL

END + RIGHT(REPLICATE( ) + CAST(ABS(ShipperID) AS varchar( )) )

WHEN THEN CompanyName

WHEN THEN Phone

ELSE NULL

END

当然 在这里你也可以使用Richard的方法 避免ORDER BY子句中列数据类型带来的问题 如果要根据ShipperID排序输出 你可以按照下面的方式调用修改后的GetSortedShippers存储过程 EXEC GetSortedShippers

四 动态执行

使用动态执行技术 我们能够更轻松地编写出GetSortedShippers存储过程 使用这种方法 我们只需动态地构造出SELECT语句 然后用EXEC()命令执行这个SELECT语句 假设传递给存储过程的参数是列的名字 存储过程可以大大缩短

ALTER PROC GetSortedShippers

@ColName AS sysname

AS

EXEC( SELECT * FROM Shippers ORDER BY + @ColName)

在SQL Server 和 中 你可以用系统存储过程sp_ExecuteSQL替代Exec()命令 BOL说明了使用sp_ExecuteSQL比使用Exec()命令更有利的地方 一般地 如果满足以下三个条件 你能够在不授予存储过程所涉及对象权限的情况下 授予执行存储过程的权限

首先 只使用Data Manipulation Language(DML)语言(即SELECT INSERT UPDATE DELETE)其次 所有被引用的对象都有与存储过程同样的所有者第三 没有使用动态命令

上面的存储过程不能满足第三个条件 在这种情况下 你必须为所有需要使用存储过程的用户和组显式地授予Shippers表的SELECT权限 如果这一点可以接受的话 一切不存在问题 类似地 你可以修改存储过程 使它接受一个列号参数 如Listing 所示

【Listing 用列号作为参数 动态执行(代码较长的方法)】

ALTER PROC GetSortedShippers

@ColNumber AS int

AS

DECLARE @cmd AS varchar( )

SET @cmd = SELECT * FROM Shippers ORDER BY +

CASE @ColNumber

WHEN THEN ShipperID

WHEN THEN CompanyName

WHEN THEN Phone

ELSE NULL

END

EXEC(@cmd)

注意 当你使用了函数时 你应该在一个变量而不是EXEC()命令内构造SELECT语句 此时 CASE表达式动态地确定使用哪一个列 还有一种更简短的格式 T SQL允许在ORDER BY子句中指定SELECT清单中列的位置 如Listing 所示 这种格式遵从了SQL 标准 但ANSI SQL 标准不支持这种格式 所以最好不要使用这种格式

【Listing 列号作为参数 动态执行(代码较短的方法)】

ALTER PROC GetSortedShippers

@ColNumber AS int

AS

DECLARE @cmd AS varchar( )

SET @cmd = SELECT * FROM Shippers ORDER BY + CAST(@ColNumber AS varchar( ))

EXEC(@cmd)

五 用户定义函数

如果你使用的是SQL Server 想要编写一个用户定义的函数(UDF) 这个用户定义函数接受列的名字或编号为参数 返回排序的结果集 Listing 显示了大多数程序员当成第一选择的方法

【Listing 列名字作为参数 使用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 THEN

WHEN THEN +

WHEN THEN +

ELSE NULL

END + RIGHT(REPLICATE( ) + CAST(ABS(ShipperID) AS

varchar( )) )

WHEN CompanyName THEN CompanyName

WHEN Phone THEN Phone

ELSE NULL

END

但是 SQL Server不接受这个函数 它将返回如下错误信息

Server: Msg Level State Procedure ufn_GetSortedShippers Line

The ORDER BY clause is invalid in views inline functions and

subqueries unless TOP is also specified

注意错误信息中的 unless SQL Server 不允许在视图 嵌入式UDF 子查询中出现ORDER BY子句 因为它们都应该返回一个表 表不能指定行的次序 然而 如果使用了TOP关键词 ORDER BY子句将帮助确定查询所返回的行 因此 如果指定了TOP 你还可以同时指定ORDER BY 由于在带有TOP的UDF中允许使用ORDER BY子句 你可以使用一个技巧 把 SELECT * 替换成 SELECT TOP PERCENT * 这样 你就能够成功地构造出一个接受列名字或编号为参数 返回排序结果的函数

新构造的函数可以按照如下方式调用

SELECT * FROM ufn_GetSortedShippers( ShipperID )

lishixinzhi/Article/program/SQL/201311/16169

需要用sql语句来处理。

比如原表数据如下：

现在要按出生日期从早到晚排列，并给出序号：

sql语句如下：

select t.* from

(select t1.*,(select count(*)+1 from 表2 as t2 where t2.出生日期<t1.出生日期) as 序号 from 表2 as t1) as t

order by 序号

结果

先Union,再Order by.

select *

from t1

where 名字='王艳'

union all

select *

from t2

where 名字='王艳'

.

.

.

order by 时间

---