oracle 数据库查询排序小结

oracle 数据库中，列的合并用法:  例如有表中有First_Name、Last_Name 两列，若想显示的时候将这两列合并则只需要First_Name||Last_Name或First_Name||‘ ’||Last_Name。

判断数值范围可以使用between and 实现。如 select XXX from XXX where salary between 10 and 100。

在查询过程中，若是想要给排序的结果编号可以使用：rank()、dense_rank()、row_number()、rownum实现。

rank()、dense_rank()、row_number()的不同点：

rank()    1 2 3 3 5 6 7 、dense_rank() 1 2 3 3 4 5 6、row_number() 1 2 3 4 5 6 7

用法相同，均为 select  rank() over( partition by XXX order by XXX )  from.....

partition by xxx   为以xxx分组，不同于group by 的是它并不将xxx作为唯一一列显示，而是将xxx属性分成多个组，但是仍然在一张总表中显示。

例如若要分组排序则可以用partition by实现。如:partition by department_ID order by salary desc.  意味着按部门分组后按薪水的大小降序排列。但结果显示在一张表中。

rownum 为结果集中行的索引，从1开始，注意的是 

1.无法使用如 rownum >2 的命令.因为rownum必须要从1开始. 

2.rownum计算在排序与where之前,，且默认为1。   

如select * from emp where rownum <  3 order by sal;  ---- 首先选出当前emp表的前3项,然后在按照sal排序.    

   select * from (select * from emp order by sal) where rownum < 3   ----这样才是选出emp表中,工资最低的3个人

3.内查询结果的rownum与外部查询结果的rownum不同，若要使用内层的rownum则要讲其重命名。

select department_ID, Salary, rownum as salrank from HR.EMPLOYEES where department_ID = 60 order by salary;

原表结构:

查询后结构:

另一种行的结构ROWID，数据库中每一个行都会有一个默认的且唯一的ROWID。可以快速的定位具体的数据位置。

使用rowid快速唯一确定重复行结合max或min函数来实现删除重复行。

SQL>delete from stu a where rowid not in (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);                                          //这里max使用min也可以

或者用下面的语句

SQL>delete from stu a where rowid < (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);       //这里如果把max换成min的话，前面的where子句中需要把"<"改为">"

跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。

SQL>delete from stu where rowid not in (select max(rowid) from stu t group by t.no, t.name, t.sex );

分层查询：select level as position, Employee_ID, First_Name, Last_Name, Manager_ID from employees 

start with First_Name||' '||Last_Name 
='David Austin' connect by prior Manager_ID = Employee_ID order by 
Level desc;

prior 标识为旧键值，被prior标识的Manager_ID为‘David Austin’的属性，即旧键或上层的属性。

level 标识查询层次的深度，初始为1。

oracle 数据库中，列的合并用法:  例如有表中有First_Name、Last_Name 两列，若想显示的时候将这两列合并则只需要First_Name||Last_Name或First_Name||‘ ’||Last_Name。

判断数值范围可以使用between and 实现。如 select XXX from XXX where salary between 10 and 100。

rank()、dense_rank()、row_number()的不同点：

rank()    1 2 3 3 5 6 7 、dense_rank() 1 2 3 3 4 5 6、row_number() 1 2 3 4 5 6 7

用法相同，均为 select  rank() over( partition by XXX order by XXX )  from.....

partition by xxx   为以xxx分组，不同于group by 的是它并不将xxx作为唯一一列显示，而是将xxx属性分成多个组，但是仍然在一张总表中显示。

例如若要分组排序则可以用partition by实现。如:partition by department_ID order by salary desc.  意味着按部门分组后按薪水的大小降序排列。但结果显示在一张表中。

rownum 为结果集中行的索引，从1开始，注意的是 rownum计算在排序之后  rownum 计算在where之后 默认为1   内查询结果的rownum与外部查询结果的rownum不同，若要使用内层的rownum则要讲其重命名。

分层查询：select level as position, Employee_ID, First_Name, Last_Name, Manager_ID from employees 

start with First_Name||' '||Last_Name 
='David Austin' connect by prior Manager_ID = Employee_ID order by 
Level desc;

prior 标识为旧键值，被prior标识的Manager_ID为‘David Austin’的属性，即旧键或上层的属性。

level 标识查询层次的深度，初始为1。

行列转换操作 用with、decode或with、case

with TT as (select Deptno,job, avg(sal)as avgsal from SCOTT.emp group by deptno,job) 
select DName,sum(decode(job,'MANAGER',avgsal, null)) as MANAGER,
sum(decode(job,'CLERK',avgsal, null)) as CLERK,
sum(decode(job,'SALESMAN',avgsal, null)) as SALESMAN,
sum(decode(job,'PRESIDENT',avgsal, null)) as PRESIDENT,
sum(decode(job,'ANALYST',avgsal, null)) as ANALYST from TT natural join SCOTT.DEPT group by DName;

或

with TT as (
     select deptno,
     case job when 'MANAGER' then avg(sal) else null end as T1,
      case job  when 'CLERK' then avg(sal) else null end as T2,
      case job    when 'SALESMAN' then avg(sal) else null end as T3,
      case job      when 'PRESIDENT' then avg(sal) else null end as T4,
      case job        when 'ANALYST' then avg(sal) else null end as T5
                 from SCOTT.EMP group by job,deptno
)
select Dname, NVL(to_char(sum(T1)),'无') as MANAGER, NVL(to_char(sum(T2)),'无') as CLERK, NVL(to_char(sum(T3)),'无') as SALESMAN, NVL(to_char(sum(T4)),'无') as PRESIDENT,
NVL(to_char(sum(T5)),'无') as ANALYST from TT natural join SCOTT.dept group by DNAme;

原理均是先用判断语句decode 或case 判断原列属性job是否为某个值（新列属性），填充avgsal，若是，填充avgsal,否则填充null。最后用合并函数sum来将多个相同的行合并，对应的列属性相加。

这是sum合并之前，可以看出存在相同的deptno

合并之后可以看到相同行均被合并。

NVL(xxx,XXX)表示若xxx为空则输出XXX。但要求xxx与XXX的类型要一致。若xxx为空时输出无。则应该为NVL(to_char(xxx),'无')。

还有pivot函数可以用来行列转换

select * from (

(select avg(sal) as avgsal,job,deptno from emp group by job,deptno)

pivot sum(avgsal) for job in (

  when 'MANAGER' then avgsal,

when 'CLERK' then avgsal,

                when 'SALESMAN' then avgsal,

                      when  'PRESIDENT' then avgsal,

                            when 'ANALYST' then avgsal

)

);