Oracle排序

order by 默认情况下是升序排列

deptno是降序排列

select * from dept order by desc;

先进行过滤，在进行排序（除deptno为10的部门）
SQL>select ename,empno from emp where deptno<>10 order by empno asc; deptno进行升序排列
SQL>select ename,sal,deptno from emp order by deptno asc;
deptno进行升序排列的前提下，ename进行降序排列
SQL>select ename,sal,deptno from emp order by deptno asc,ename desc;

----------------------------------------------------------------------------------------------------------------

一、当使用分组函数时，分组函数只能出现在选择列表、order by 和 having 子句中，而不能出现在 where 和 group by 子句中。另外，使用分组函数还有以下一些注意事项：

    1、  当使用分组函数时，除了函数 count(*) 之外，其他分组函数都会忽略 null 行，当然 count(列名) 也会忽略 null 行。

    2、  当执行 select 语句时，如果选择列表时包含列、表达式和分组函数，那么这些列和表达式必须出现在 group by 子句中。

    3、  当使用分组函数时，在分组函数中可以指定 all 和 distinct 选项。其中 all 是默认选项，该选项表示统计所有行数据（包括重复行）；如果指定 distinct，则只会统计不同行值。

    4、  Having 是在 group by 语句之后。

 

二、group by 用于对查询结果进行分组统计，而 having 子句则用于限制分组显示结果。注意，如果在选择列表中同时包含列、表达式和分组函数，那么这些列和表达式必须出现在 group by 子句中。

 

三、使用 group by 子句、where 子句和分组函数有以下一些注意事项：

    1、分组函数只能出现在选择列表、having 子句和 order by 子句中

    2、如果在select 语句中同时包含 group by 、having 以及 order by 子句，则必须将 order by 子句放在最后。默认情况下，当使用 group by 子句统计数组时，会自动按照分组列的升序显示统计结果。通过使用 order by 子句，可以改变数据分组的排序方式。

    3、如果选择列表包含有列、表达式和分组函数，那么这些列和表达式必须出现在 group by 子句中，否则会显示错误消息。

    4、当限制分组显示结果时，必须要使用 having 子句，而不能在 where 子句中使用分组函数限制显示结果，否则会显示错误信息。

 

------------------------oracle中分组排序函数用法-----------------------

项目开发中，我们有时会碰到需要分组排序来解决问题的情况，如：

1、要求取出按field1分组后，并在每组中按照field2排序；

2、亦或更加要求取出1中已经分组排序好的前多少行的数据

这里通过一张表的示例和SQL语句阐述下oracle数据库中用于分组排序函数的用法。

a、row_number() over()

row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。 

b、rank() over()

rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

c、dense_rank() over()

dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。

 

示例：

如有表Test，数据如下

 

Java代码  

1. CREATEDATE      ACCNO   MONEY  
2. 2014/6/5           111      200   
3. 2014/6/4           111      600   
4. 2014/6/5           111      400   
5. 2014/6/6           111      300   
6. 2014/6/6           222      200   
7. 2014/6/5           222      800   
8. 2014/6/6           222      500   
9. 2014/6/7           222      100   
10. 2014/6/6           333      800   
11. 2014/6/7           333      500   
12. 2014/6/8           333      200   
13. 2014/6/9           333      0  

  

比如要根据ACCNO分组，并且每组按照CREATEDATE排序，是组内排序，并不是所有的数据统一排序，

用下列语句实现：

 

 

Sql代码  

1. select t.*,row_number() over(partition by accno order by createDate) row_number from Test t  

 

查询结果如下：

 

 

 

大家可以注意到ACCNO为111的记录有两个相同的CREATEDATE，用row_number函数，他们的组内计数是连续唯一的，但是如果用rank或者dense_rank函数，效果就不一样，如下：

rank的sql：

Sql代码  

1. select t.*,rank() over(partition by accno order by createDate) rank from Test t  

 

查询结果：

 

可以发现相同CREATEDATE的两条记录是两个第2时接下来就是第4.

 

dense_rank的sql：

Sql代码  

1. select t.*,dense_rank() over(partition by accno order by createDate) dense_rank from Test t  

 

查询结果：

 

可以发现相同CREATEDATE的两个字段是两个第2时接下来就是第3.

 

项目中特殊的业务需求可能会要求用以上三个不同的函数，具体情况具体对待。

 

 

再比如有时会要求分组排序后分别取出各组内前多少的数据记录，sql如下：

Sql代码  

1. select createDate,accno,money,row_number from  (select t.*,row_number() over(partition by accno order by createDate) row_number from Test t) t1 where row_number<4  

  

 

查询结果如下：

------------------------输出Oracle排序后的顺序编号 -----------------------

【语法】ROW_NUMBER()   OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】

            表示根据COL1分组，在分组内部根据 COL2排序，

             而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）

             row_number() 返回的主要是“行”的信息，并没有排名

主要功能：用于取前几名，或者最后几名等 

例1：
   select name,seqno,description

       from(select name,seqno,description,

                         row_number() over (partition by name order by seqno) id

                from table_name) 

            where id<=3;

例2：
     update table_name11 s

           set s.sortid =

                   ( with tmp 

                        as

                            (

              select r.property,ROW_NUMBER() OVER (PARTITION BY r.ifnotnull ORDER BY to_number(r.sortid)) n 

           from table_name22  r 

               where 条件1) 

                    select n 

                          from tmp t 

                                   where 条件2) 

                          where 条件3;   

     update table_name11 s set s.sortid = ( with tmp as (select r.property,ROW_NUMBER() OVER (PARTITION BY r.ifnotnull ORDER BY to_number(r.sortid)) n from table_name22  r where 条件1) select n from tmp t where 条件2) where 条件3;

 

   备注：

      使用ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)后，不要在使用order by，否则顺序不正确。