case when 中的 order by

 

今天在公司查看一段sql SP代码，发现了一段比较奇怪的代码。

大概长这样子：

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

 

查询结果

我们可以看到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

 

执行结果

我们可以看到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

我们可以猜一下执行结果。

第一个CASE WHEN 当IP等于192.168.130.85的我们返回了100 ，不等于192.168.130.85 的我们返回了1000

第二个CASE WHEN 当IP等于192.168.130.87的我们返回了2 ，不等于192.168.130.87 的我们返回了10

结果应该 192.168.130.87 被排在第一 ，因为第一个case when返回1000 但是被第二个CASE WHEN返回是2覆盖掉了，2最小的，所以排在第一。

 

哈哈哈。。。就是这样。。。

其实，执行结果是这样的。

 

我们可以看到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