Oracle 行转列后横向汇总，竖向汇总

自从某人发觉我的行转列应用的很顺溜；

某人就开始勾画各种统计效果图；

原始需求：只需要统计数据行转列；

需求加强：需要横向统计结果；

需求同步持续加强：需要纵向统计结果；

简化需求描述：咱们家有N位家庭成员，这些成员狼吞虎咽了某些种类的水果；

要求统计：①每个家庭成员分别吃了多少水果

②不能种类的水果分别被吃了多少个

③一共吃了多少个水果

首先建立水果表：

create table fruit_table(

id NUMBER(11) not null,

name varchar2(50)

);

insert into fruit_table(id,name) values('1','苹果');

insert into fruit_table(id,name) values('2','香蕉');

insert into fruit_table(id,name) values('3','水蜜桃');

其次建立成员表：

create table family_table(

id NUMBER(11) not null,

username varchar2(50)

);

insert into family_table(id,username) values('11','爸爸');

insert into family_table(id,username) values('12','妈妈');

insert into family_table(id,username) values('13','大宝宝');

insert into family_table(id,username) values('14','小宝宝');

最后建立成员吃水果统计表：

create table eat_fruit_table(

fruitid NUMBER(11) not null,

familyid NUMBER(11) not null,

num number(6)

);

insert into eat_fruit_table(fruitid,familyid,num) values('1','11',3);

insert into eat_fruit_table(fruitid,familyid,num) values('1','12',2);

insert into eat_fruit_table(fruitid,familyid,num) values('1','13',1);

insert into eat_fruit_table(fruitid,familyid,num) values('1','14',1);

insert into eat_fruit_table(fruitid,familyid,num) values('2','11',1);

insert into eat_fruit_table(fruitid,familyid,num) values('2','12',2);

insert into eat_fruit_table(fruitid,familyid,num) values('2','13',3);

insert into eat_fruit_table(fruitid,familyid,num) values('2','14',3);

insert into eat_fruit_table(fruitid,familyid,num) values('3','11',0);

insert into eat_fruit_table(fruitid,familyid,num) values('3','12',2);

insert into eat_fruit_table(fruitid,familyid,num) values('3','13',1);

insert into eat_fruit_table(fruitid,familyid,num) values('3','14',1);

行转列查询sql:

SELECT * FROM (

select familyid,c.username,苹果,香蕉,水蜜桃,nvl(苹果,0)+nvl(香蕉,0)+nvl(水蜜桃,0) fruitnum from (SELECT a.fruitid,a.familyid,sum(a.num) num FROM eat_fruit_table a group by a.fruitid,a.familyid)

pivot(sum(num) for fruitid in('1' as 苹果, '2' as 香蕉,'3' as 水蜜桃) ) b left join family_table c on b.familyid = c.id

union all

select 999999 familyid,'总计' ,苹果,香蕉,水蜜桃,nvl(苹果,0)+nvl(香蕉,0)+nvl(水蜜桃,0) from (SELECT a.fruitid,sum(a.num) num FROM eat_fruit_table a group by a.fruitid)

pivot(sum(num) for fruitid in('1' as 苹果, '2' as 香蕉,'3' as 水蜜桃) ) b

);

思路：先统计出每个家庭成员吃水果的数量，其次再统计每种水果被吃了几个通过union all合并，查询；

注意事项：

※ 两次group by 分组的数据不同

※ as 苹果中的苹果不带单引号，返回的苹果也不用带引号且前面不加别名；

※ nvl(苹果,0) 有时候统结果为空，可以用nvl做判断