《SQL Cookbook》 - 第二章查询结果排序

╰+哭是因爲堅強的太久メ 2022-09-04 12:48 147阅读 0赞

1. 默认情况下，ORDER BY会做升序排列，因此ASC子句是可选的。可以通过DESC执行降序排列。可以再ORDER BY子句中列出不同的排序列，逗号分隔。

一般而言，可以根据一个没有被包含在SELECT列中的列来排序，但是必须明确地指定列名。但是当查询语句中含有GROUP BY或者DISTINCT，那么不能按照SELECT列表之外的列进行排序。该问题可参考《[小白学习MySQL - only\_full\_group\_by的校验规则][MySQL - only_full_group_by]》。

2. 依据子串排序

按照职位字段后3个字符进行排序，

DB2、MySQL、Oracle、PG，

select ename,job
      from emp
     order by substr(job,length(job)-2);

SQL Server，第三个参数，既可以是2，还可以大于2，

select ename,job
      from emp
     order by substring(job,len((job)-2,2);

3. 如果字段混合了字母和数字，其中按照字母或者数字进行排序，例如，

create view V
        as
    select ename||' '||deptno as data
      from emp;

使用translate函数和replace函数删除每一行的数字或者字符，即可按照剩余的部分排序。

需求按照ename或者deptno进行排序，

Oracle、PG，按照deptno排序，

select data
     from V
     order by replace(data,
                   replace(
                 translate(data, '0123456789','##########','#',''),'');

按照ename排序，

select data
      from V
     order by replace(
                   translate(data,'0123456789','##########','#','');

DB2，按照deptno排序，

select *
      from (
    select ename||' '||cast(deptno as char(2)) as data
      from emp
              ) v
     order by replace(data,
                   replace(
                 translate(data,'##########','01234567890',),'#',''),'');

按照ename排序，

select *
      from (
    select ename||' '||cast(deptno as char(2)) as data
      from emp
              ) v
     order by replace(data,
                 translate(data,'##########','01234567890',),'#','');

MySQL和SQL Server不支持translate函数。

4. NULL的排序

DB2、MySQL、PG和SQL Server，需要增加辅助列，

非Null值按照comm升序排列，全部Null值放到最后，删除desc，则放到最前，

select ename, sal, comm
      from (
    select ename, sal, comm,
              case when comm is null then 0 else 1 end as is_null
      from emp
              ) x
     order by is_null desc, comm;

Oracle，提供了特殊语法，无需考虑非Null值的排序方式，

非Null值comm升序排列，全部Null值放到最后，

select ename, sal, comm
      from emp
     order by comm nulls last;

非Null值comm升序排列，全部Null值放到最前，

select ename, sal, comm
      from emp
     order by comm nulls first;

5. 依据条件逻辑动态调整排序项

例如，如果job等于salesman，就需要按照comm排序，否则，就按照sal排序，此时用到case...when...子句，

select ename, sal, job, comm
      from emp
     order by case when job='SALESMAN' then comm else sal end;

或者，

select ename, sal, job, comm,
              case when job='SALESMAN' then comm else sal end as ordered
      from emp
     order by 5;

**《SQL Cookbook》读书笔记：**

[《SQL Cookbook》 - 第一章 检索数据][SQL Cookbook_ - _]

**近期更新的文章：**

《[《SQL Cookbook》 - 第一章 检索数据][SQL Cookbook_ - _]》

《[梅西 ≠ 一人一城？][Link 1]》

《[小白学习MySQL - 表空间碎片整理方法][MySQL -]》

《[Oracle字符串类型扩容隐患][Oracle]》

《[PG逻辑复制的REPLICA IDENTITY设置][PG_REPLICA IDENTITY]》

**文章分类和索引：**

[《公众号800篇文章分类和索引][800]》

[MySQL - only_full_group_by]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a070ddf52966a270c13763d5068d58ca8c16a3655f088ddb1c856b3f2394178452b751b2&idx=1&mid=2247492995&scene=21&sn=bfeca96384e6310326f2309da5ae0853#wechat_redirect
[SQL Cookbook_ - _]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a011ddf529079fa6fbcfbee70fd98db135076344d0ae9ea4863b3fe9f5e17149aac2609b&idx=1&mid=2247493090&scene=21&sn=8bc441cc9e023eb49e897759355f6357#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a029ddf5293fbd6d72536d566769b6ef0dff0d6f438363ece58c99c57a68d5a93e9796e1&idx=1&mid=2247493082&scene=21&sn=1adcc88366ab2baa5b2e6767941994d3#wechat_redirect
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a030ddf52926dd6c3a22a7da1a3c7055d8e24e1ee958d885b0c25d29202239587a2a1a66&idx=1&mid=2247493059&scene=21&sn=16eaffcc25f1e7d50c792e58b3a230ea#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a055ddf52943c9699cd6e4ccd7ab18c6f181921a97cf9c35cb962b9898c6eed1a8fb553f&idx=1&mid=2247493030&scene=21&sn=5bc4c2487b1ac96d507ce7468f02d535#wechat_redirect
[PG_REPLICA IDENTITY]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0b7ddf529a1c70e180e944651c2330c2be943b1ef07c95b78b9936a2d32c43b166b9637&idx=1&mid=2247492932&scene=21&sn=b3dc4ee09e8517446d356e31c4b17a87#wechat_redirect
[800]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a084ddf529920f2572e843967c4cb4248b6adc854a001315dd54264080349d3a5cc2ea14&idx=1&mid=2247492983&scene=21&sn=5e0514b86258eebed20740dce83a6d45#wechat_redirect