前言:
此时你们对“oracle 列值合并”大概比较注意,各位老铁们都需要了解一些“oracle 列值合并”的相关内容。那么小编也在网上汇集了一些有关“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做判断
标签: #oracle 列值合并