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

在大众消息组中，一个常常展示的题目是“还好吗本领按照传播给保存进程的参数归来一个排序的输入？”。在少许高程度大师的扶助之下，我整治出了这个题目的几种处置计划。 一、用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表白式和动静实行本领的百般计划。