案例陈述场景 [英] Case statement Scenario

问题描述

大家好，

在以下脚本中，我无法跟踪其工作方式.
谁能帮我吗?

代码如下:

Hi Everyone,

In the following script I couldn''t trace out how it is working.
Can anyone please help me?

Code goes like this:

select *,ROW_NUMBER() over (order by case  @sortorder
                                           when ''asc'' then case @sortexpression 
                                           when ''empno'' then empno
                                           when ''ename'' then ename
                                           end end asc,
                                case @sortorder 
                                           when ''desc'' then case @sortexpression
                                           when ''empno'' then empno
                                           when ''ename'' then ename  
                                           end end desc ) from emp

假设如果案例是"ASC"，那么案例"DESC"会发生什么.它的价值是什么?

问候
Chaithanya M

Supose if case is ''ASC'' what happens to the case ''DESC''.what will be it''s value?

Regards
Chaithanya M

推荐答案

<pre lang="sql">declare @sortorder varchar(10) = ''desc'';
declare @sortexpression varchar(10) = ''ename'';
select *, ROW_NUMBER() over (order by case  @sortorder
                                           when ''asc'' then case @sortexpression
-- changed to VARCHAR to match all sort types
                                           when ''empno'' then CAST([EmployeeID] AS VARCHAR(10))
                                           when ''ename'' then [LastName]
                                           end end asc,
                                case @sortorder
                                           when ''desc'' then case @sortexpression
                                           when ''empno'' then CAST([EmployeeID] AS VARCHAR(10))
                                           when ''ename'' then [LastName]
                                           end end desc )
from [AdventureWorks2008R2].[dbo].[MyEmployees]

尝试使用变量的各种值来运行它.排序是按照顺序进行的，首先是第二.

如果只想使用一个顺序值运行，则需要更改CASE语句.

Try running this with various values for your variables. The sort is done in order first, second.

If you want to run with only one order value then the CASE statements would need changed.

首先执行内部案例，然后执行外部案例.
编写此类复杂表达式的最佳方法是放在方括号内-始终使代码易于阅读.

如果大小写为asc，则根据表达式@ sortexpressions empno或ename选择.
某些条件适用于desc.

The inner case will execute first and then the outer ones execute.
The best way to write such complex expressions is to put brackets - that always makes code easier to read.

When the case is asc then based on the expression @sortexpressions empno or ename is chosed.
Some conditions are applicable to desc.

这篇关于案例陈述场景的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！