数据库查询功效的动态排序
发布时间：2018-08-15 13:15:52 所属栏目：电商 来源：站长网
导读：在民众消息组中，一个常常呈现的题目是奈何才气按照转达给存储进程的参数返回一个排序的输出？。在一些高程度专家的辅佐之下，我清算出了这个题目的几种办理方案。 一、用IF...ELSE执行预先编写好的查询 对付大大都人来说，起首想到的做法大概是：通过IF..
|
在民众消息组中，一个常常呈现的题目是“奈何才气按照转达给存储进程的参数返回一个排序的输出？”。在一些高程度专家的辅佐之下，我清算出了这个题目的几种办理方案。 一、用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表达式和动态执行手段的各类方案。 （编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
