drop table aa_test;
create table aa_test(
id number,
name varchar2(100 byte),
pid number default 0
);
INSERT INTO aa_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO aa_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO aa_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO aa_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO aa_TEST(ID,NAME,PID) VALUES('5','121','2');
insert into aa_test(id,name,pid) values('6','six','4');
--子递归查询一般用于树形结构的应用(有点像自连接)
--root-->末梢
select aa_test.*,level from aa_test start with id=1
connect by prior id=pid
order by pid;
--末梢-->root
select * from aa_test
start with id=6
connect by prior pid=id;
--可通过level 关键字查询所在层次,prior获得前驱
select first_value(pid) over(order by level desc rows unbounded
preceding) as firstpid from aa_test
start with id=6
connect by prior pid=id
----------------------------------
delete from emp where ename='kevin';
select deptno,ename,sal
from emp
order by deptno;
--连续求和 sum(sal) over(order by ename)
--非连续 sum(sal) over() ==sum(sal) 求总
select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) 连续求和,
sum(sal) over(order by ename) 总和,
sum(sal) over(order by deptno desc,sal desc) "44总和",
100*round(sal/sum(sal) over(),4) "份额(%)"
from emp;
sum(sal) over(partition by deptno order by ename) --按部门连续求和
sum(sal) over(partition by deptno) --按部门连续求和
sum(sal) over(order by deptno,ename) --不按部门??
sum(sal) over()
--------------------
create table xman
(
name varchar2(20),
city varchar2(20),
money number(6)
);
-------------
insert into xman values('zhang3','HONGKONG',100);
insert into xman values('zhang3','MACAO',200);
insert into xman values('zhang3','BEIJING',300);
insert into xman values('li4','HONGKONG',300);
insert into xman values('li4','MACAO',100);
insert into xman values('li4','BEIJING',400);
insert into xman values('wang5','HONGKONG',200);
insert into xman values('wang5','MACAO',700);
--汇总(双向)
select name,city,sum(money) as money from xman
group by cube(name,city);
--汇总(单项)
select nvl(name,'总计'),nvl(city,'小计'),sum(money) as money from xman
group by rollup(name,city);
select * from xman;
--先看如下的一基础查询,实现行列转换:
SELECT nvl(name, 'zzYSUM') name,
SUM(decode(city, 'HONGKONG', money, 0)) hongkong, --横向汇总数据
SUM(decode(city, 'MACAO', money, 0)) macao,
SUM(decode(city, 'BEIJING', money, 0)) beijing,
SUM(decode(city, 'ZongGong', money, 0)) xsum
FROM
(SELECT name, nvl(city, 'ZongGong') city, SUM(money) money --纵向汇总
FROM xman
GROUP BY rollup(name, city))
GROUP BY name order by name;
--再看下面实现横向,纵向统计:
WITH x AS
(SELECT nvl(name, 'zzYSUM') name,
SUM(decode(city, 'HONGKONG', money, 0)) hongkong,
SUM(decode(city, 'MACAO', money, 0)) macao,
SUM(decode(city, 'BEIJING', money, 0)) beijing,
SUM(decode(city, 'ZongGong', money, 0)) xsum
FROM
(SELECT name, nvl(city, 'ZongGong') city, SUM(money) money
FROM xman
GROUP BY rollup(name, city))
GROUP BY name order by name)
SELECT *
FROM x WHERE name <> 'zzYSUM'
UNION
SELECT 'zzYSUM',
SUM(hongkong) hongkong,
SUM(macao) macao,
SUM(beijing) beijing,
SUM(xsum) sum
FROM x
WHERE name <> 'zzYSUM';
分享到:
相关推荐
常用 oracle函数汇总大全,很实用,基本都是常用到的
oracle的分析函数汇总oracle的分析函数汇总
Oracle常用函数使用汇总
Oracle 分析函数详解 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
oracle最全函数大全(分析函数-聚合函数-转换函数-日期型函数-字符型函数-数值型函数-其他函数
oracle数据仓库分析函数汇总:over oracle 分析函数 数据仓库:带了测试数据,由浅到深圳分析数据仓库的分析函数,是BI入门的好帮手
Oracle常用函数汇总 * SQL Group Functions (num can be a column or expression) * (null values are ignored, default between distinct and all is all) * Miscellaneaous Functions
oracle常用函数汇总,每个函数下面都给出例子,易于理解,收藏此文档,忘记时可以快速查询到哦
Oracle常用函数收集 SQL中的单记录函数
收集的最全的ORACLE 函数文档,word格式,打印、查看方便,非常实用。
ORACLE函数大全汇总详解(20100915补充修订版) 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数,这些函数都可被称为SQL或PL/SQL语句
以下是对oracle中的常用函数进行了汇总介绍,需要的朋友可以过来参考下
oracle内部函数的一个简单汇总,供初学者使用
主要罗列了Oracle数据库的一些基本函数的说明和示例讲解,用于介绍关于Oracle数据的基本操作,适用于学习Oracle数据库的人。
oracle中涉及到的日期、时间所有函数
Oracle Exception汇总(自定义Oracle异常) 使用方法举例: Exception When no_data_found then Dbms_output.put_line(‘no_data_found’); ACCESS_INTO_NULL 为对象赋值前必需初始化对象。对应ORA-06530错误。 CASE...
oracle 时间函数汇总,详细介绍了oracle时间的截取,oracle时间函数年、月、日的具体使用,实用性很强。
从论坛搜到的,感觉还...1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数