SQL SERVER 中的 ORDER BY AND CASE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20096572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORDER BY AND CASE IN SQL SERVER
提问by user2363025
I need to have an order by functionality inside a stored procedure. A value is posted to a webservice and based on that value I have to order the results in a certain way i.e.
我需要在存储过程中按功能排序。一个值被发布到一个网络服务，并基于该值我必须以某种方式对结果进行排序，即
When ColName is posted order by ColName When ColName2 is posted order by ColName2
当 ColName 按 ColName 发布顺序 当 ColName2 发布时 按 ColName2 排序
I was looking into using Case but I am getting an error:
我正在考虑使用 Case，但出现错误：
Incorrect syntax near '@version'
ORDER BY CASE
WHEN @OrderBy ='Seller (code)' THEN A_SNO
WHEN @OrderBy ='Lot' THEN A_LOTNO
WHEN @OrderBy ='Ring Type' THEN RN_NUM
WHEN @OrderBy ='Aim Error Code' THEN AimRejectionCode
ELSE A_SNO END
DECLARE @version varchar(50)
SET @version = (SELECT DBVERSION FROM MSYSCFG)
PRINT 'New Version = ' + @version
Sorry I'm new to this and using sql server 2008. Any help appreciated UPDATE: Provided additional code. When I leave out the last 3 lines I get an error of
对不起，我是新来的，使用 sql server 2008。感谢任何帮助更新：提供了额外的代码。当我省略最后 3 行时，我得到一个错误
Incorrect synatx near END
UPDATE2: I've changed the ORDER BY TO the following:
UPDATE2：我已将 ORDER BY 更改为以下内容：
ORDER BY
CASE @OrderBy
WHEN @OrderBy = 'Seller (code)' THEN A_SNO
WHEN @OrderBy = 'Lot' THEN A_LOTNO
WHEN @OrderBy = 'Aim Error Code' THEN AimRejectionCode
END
, CASE @OrderBy WHEN 'Ring Type' THEN RingTypeFlag
END
, A_SNO
The first three are varchar and the other is of type int.
This is giving me red lines under all three '=' with an error description of:
'incorrect syntax near '='and a red line under ORDER BY which gives an error description of:
前三个是 varchar，另一个是 int 类型。这给了我所有三个 '=' 下的红线，错误描述为：
'incorrect syntax near '='和 ORDER BY 下的红线，它给出了错误描述：
'A constant expression was encountered in the ORDER BY list, position 3'
Note when I remove the final , A_SNOThe Order By error is gone but I am still receiving the = syntax error
请注意，当我删除最后, A_SNO的 Order By 错误时，我仍然收到 = 语法错误
回答by Damien_The_Unbeliever
CASEis an expressionand has to produce a result of a single well defined type. So as long as the types of all columns are compatible, they can all be placed into a single CASEexpression.
CASE是一个表达式，必须产生一个明确定义的类型的结果。所以只要所有列的类型兼容，它们都可以放在一个CASE表达式中。
If that's notthe case then you need to split it up and use multipleexpressions. Say that Col1and Col3have compatible types (whether the same or you're happy for one to convert to the other) and that Col2and Col4have incompatible types (both between themselves and with Col1and Col3), then we need threeexpressions:
如果不是这种情况，那么您需要将其拆分并使用多个表达式。假设Col1并Col3具有兼容的类型（无论是相同的还是您乐于将一种转换为另一种）Col2并且Col4具有不兼容的类型（在它们之间以及 withCol1和Col3），那么我们需要三个表达式：
ORDER BY
CASE @OrderBy
WHEN 'Col1' THEN Col1
WHEN 'Col3' THEN Col3
END
, CASE @OrderBy WHEN 'Col2' THEN Col2 END
, CASE @OrderBy WHEN 'Col4' THEN Col4 END
, Col1
(I've also include a final expression of Col1so that your "fallback" sort still occurs)
（我还包含了一个最终表达式，Col1以便您的“后备”排序仍然发生）
For each of the CASEexpressions above, if no match occurs then the expression returns NULL- and all NULLs sort together, so that that entire CASEexpression then has no overall effect on the sorting.
对于CASE上面的每个表达式，如果没有匹配发生，则表达式返回NULL- 并且所有NULLs 一起排序，因此整个CASE表达式对排序没有整体影响。
From CASE:
来自CASE：
Return Types
Returns the highest precedence type from the set of types in result_expressionsand the optional else_result_expression.
返回类型
从result_expressions和可选的else_result_expression 中的类型集中返回最高优先级类型。
回答by Adminiculo
Let see if this is what you need:
让我们看看这是否是您所需要的：
DECLARE @t table(col1 int, col2 int, col3 int,col4 int);
DECLARE @OrderBy varchar(4) = 'Col3';
insert into @t
values
(1,2,3,4),
(6,3,8,5),
(7,4,1,7),
(3,7,0,1),
(9,8,3,6);
SELECT
CASE @OrderBy
WHEN 'Col2' THEN Col2
WHEN 'Col3' THEN Col3
WHEN 'Col4' THEN Col4
ELSE Col1 END as OrderCol
,*
FROM @t
ORDER BY OrderCol desc;
SELECT *
from @t
ORDER BY CASE @OrderBy
WHEN 'Col2' THEN Col2
WHEN 'Col3' THEN Col3
WHEN 'Col4' THEN Col4
ELSE Col1 END desc;
