Oracle一些特殊用法

指定排序 DECODE(按照指定的soNo顺序排序)

SELECT id,so_noFROM edi_bookingWHERE so_no IN ('TESTVENT0007', 'TESTVENT1111', 'TESTVENT0002', 'TESTVENT0003') ORDER BY DECODE(so_no, 'TESTVENT0007',1,'TESTVENT1111',2,'TESTVENT0002',3,'TESTVENT0003',4);

nvl

• nvl 把值为空的值显示修改
• count(remark) 统计的是remark值不为空的列

• nvl(remark, 0) 把remark值为空的 列的值置为 0

  select count(*),count(qty),count(remark) from mrk_order;1973    1973           450select count(*),count(qty),count(nvl(remark, 0)) from mrk_order;1973    1973           1973

• break on owner skip 2 相同的owner 只显示一次 不同德owner之间空2行

• set pagesize 30; 设置页面大小 显示30行

rollup & cube & grouping sets（做报表）

用法：group by rollup/cube/grouping sets (A, B, C)

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是

1. GROUP BY ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行 GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。
2. GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
3. group by grouping sets(A,B,C) = group by A, group by B, group by C

举例：

一、rollup

select owner,partition_by,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by owner, partition_by order by owner;

select owner,partition_by,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by rollup(owner, partition_by) order by owner;

等价于

select owner,partition_by,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by owner, partition_by union allselect owner,null,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by owner union allselect null,null,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') order by owner;

二、cube

select owner,partition_by,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by cube(owner, partition_by) order by owner;

三、grouping sets

select owner,partition_by,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by GROUPING SETS(owner, partition_by) ;