oracle汇总函数使用

llxbomlqdx

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';