SQLServer使用case when中的order by

缺乏、安全感 2021-06-26 16:06 523阅读 0赞

今天发现了一段比较奇怪的代码。

SELECT * 
    FROM   tablea 
    ORDER  BY cola, 
              CASE 
                WHEN type = 5 THEN 200 
                WHEN type = 6 THEN 300 
              END

小弟才疏学浅，咋一看到代码以为这样的：

Select * from tableA
    WHERE type=5
    ORDER BY ColA ,200

但是执行报错，同时也忘记了 `order by` 后面加数字是什么意思了。。。

果断查了一下：

示例1：

SELECT last_name, salary , hire_date
    FROM EMPLOYEES
    ORDER BY salary DESC;

示例2：

SELECT last_name,salary,hire_date
    FROM EMPLOYEES
    ORDER BY 2 DESC;

以上两个示例结果相同。  
因为`ORDER BY salary DESC`==`ORDER BY 2 DESC`  
`salary`是第二个元素，所以可以使用`2`来代替。  
**但是数字不可以使用`0`，也不可以超出查询的列。**  
例如：`select * from employers order by x;`  
如果`employers`表有九个字段，那个`X`的范围就是`1---9`  
不能是`0`，也不能是`10`.

原来`order by`后面跟数字是代表以第几列排序的意思。

但是`SP` 中用的是`200...`甚至`300....`没这么多列啊。。。

**事实证明 `order by` 后面用`case when` 并不等于 `order by 数字`**

查了相关资料，并且找个一个表来测试，

## 无`CASE WHEN` ##

`ORDER BY` 后面不使用 `CASE WHEN`

测试`sql`代码

SELECT  [ServerId]
          ,[ServerCode]
          ,[InBound]
          ,[OutBound]
          ,[IP]
          ,[PhoneArea]
          ,[Valid]
      FROM [ServerInfo] with(nolock)
      ORDER BY Location

查询结果  
![在这里插入图片描述][20181211135110751.png]  
我们可以看到IP 为`192.168.130.81` 的排在第一位

## 一个`CASE WHEN` ##

加上 `CASE WHEN` 当IP 为 `192.168.130.85` 时，我们返回`100`，其他返回`1000`.

代码如下

SELECT  [ServerId]
          ,[ServerCode]
          ,[InBound]
          ,[OutBound]
          ,[IP]
          ,[PhoneArea]
          ,[Valid]
      FROM [ServerInfo] with(nolock)
      ORDER BY Location
      ,CASE 
        WHEN  IP='192.168.130.85'
        THEN 100
        ELSE 1000
       END

执行结果  
![在这里插入图片描述][20181211135359686.png]

我们可以看到IP 为`192.168.130.81` 的已经不再第一位了，`192.168.130.85` 的被排在了第一位。

因为IP等于`192.168.130.85`的我们返回了`100` ，不等于`192.168.130.85` 的我们返回了`1000`

`100`比`1000`小，所以`192.168.130.85` 的被排在了第一位。

## 两个`CASE WHEN` ##

我们在加一个 `CASE WHEN` 当IP等于`192.168.130.87` 时，我们返回`2`，其他返回`10`.

代码如下

SELECT  [ServerId]
          ,[ServerCode]
          ,[InBound]
          ,[OutBound]
          ,[IP]
          ,[PhoneArea]
          ,[Valid]
      FROM [ServerInfo] with(nolock)
      ORDER BY Location
      ,CASE
        WHEN  IP='192.168.130.85'
        THEN 100
        ELSE 1000
       END
     ,CASE
        WHEN  IP='192.168.130.87'
        THEN 2
        ELSE 10
       END

其实，执行结果是这样的。  
![在这里插入图片描述][2018121114021943.png]

我们可以看到`192.168.130.85` 被排在第一，

`192.168.130.87`被排在第二。

其实两个`CASE WHEN`是相互不影响的。

其实两个`CASE WHEN`的`sql` 结果等价于下面的`sql`

SELECT  [ServerId]
          ,[ServerCode]
          ,[InBound]
          ,[OutBound]
          ,[IP]
          ,[PhoneArea]
          ,[Valid]
         ,CASE
              WHEN  IP='192.168.130.85'
              THEN 100
              ELSE 1000
          END AS MYORDER1
          ,CASE
              WHEN  IP='192.168.130.87'
              THEN 2
              ELSE 10
           END AS MYORDER2
      FROM [ServerInfo] with(nolock)
      ORDER BY Location , MYORDER1,MYORDER2

[20181211135110751.png]: /images/20210626/2ccfc535f682479dbe671f7b06118f4e.png
[20181211135359686.png]: /images/20210626/edafebdf476d4f4dbbbbb3b091108f66.png
[2018121114021943.png]: /images/20210626/505df111dac549fc864fbdc9b31c8ce9.png