Oracle一些特殊用法

最新推荐文章于 2022-07-21 21:40:50 发布

oaijiew

最新推荐文章于 2022-07-21 21:40:50 发布

阅读量1.6k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/wj_weijiaojiao/article/details/75258335

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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

SELECT id,so_no
FROM edi_booking
WHERE 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           450
select 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语句。如果是

GROUP BY ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行 GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。
GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
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 all
select owner,null,sum(amount) from mrk_order where created_date > to_date('2-5月-16','DD-MON-RR') group by owner 
union all
select 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) ;