Oracle 补全数据

0.最终效果

手中有这样一张汇总的数据表,汇总的方式并不是我们想要的,因为小黑,小红的数据并不完善

我们想得到这样的结果,即使该用户没用一些数据,我们也用null值补全

1.数据

手中有这样一张进货表Purchase,数据如下:

select name,type,data_num from 
(select '小白'name,'clothes'type,2 data_num from dual union all
select '小白'name,'clothes'type,2 data_num from dual union all
select '小白'name,'clothes'type,2 data_num from dual union all
select '小白'name,'clothes'type,1 data_num from dual union all
select '小白'name,'trousers'type,2 data_num from dual union all
select '小白'name,'trousers'type,4 data_num from dual union all
select '小绿'name,'clothes'type,1 data_num from dual union all
select '小绿'name,'trousers'type,4 data_num from dual union all
select '小绿'name,'trousers'type,5 data_num from dual union all
select '小绿'name,'hat'type,2 data_num from dual union all
select '小绿'name,'hat'type,3 data_num from dual union all
select '小黑'name,'clothes'type,1 data_num from dual union all
select '小黑'name,'trousers'type,1 data_num from dual union all
select '小黑'name,'trousers'type,10 data_num from dual union all
select '小黑'name,'hat'type,2 data_num from dual union all
select '小黑'name,'hat'type,1 data_num from dual union all
select '小黑'name,'hat'type,2 data_num from dual)Purchase

一张售出的表Sold,数据如下:

select name,type,data_num from 
(select '小红'name,'hat'type,1 data_num from dual union all
select '小白'name,'clothes'type,1 data_num from dual union all
select '小白'name,'trousers'type,2 data_num from dual union all
select '小绿'name,'clothes'type,2 data_num from dual union all
select '小绿'name,'trousers'type,2 data_num from dual union all
select '小绿'name,'hat'type,2 data_num from dual )Sold

2.整合

将数据以name字段汇总为一行数据,这也是很觉得的事情:

select name,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Purchase
where 1=1 /*这里加入一些限制*/ group by name

select name,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Sold
where 1=1 /*这里加入一些限制*/ group by name

3.合并

如果需求:你要将售出和进货表合并起来,也并不难,只需要新增一个dataType栏位即可:

/*这里我们手动指定dataType数据*/

select name,'Purchase'dataType,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Purchase
where 1=1 /*这里加入一些限制*/ group by name
union all 
select name,'Sold'dataType,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Sold
where 1=1 /*这里加入一些限制*/ group by name 
order by name asc,dataType desc

4.补全

数据看起来并不是我们想要的样子,因为小黑只有Purchase表的数据,小红只有Sold表的数据,我们想要的效果是:哪怕小黑在Sold表,小红在Purchase表里没有数据,也要显示出来.

我们需要Sold表的数据补全Purchase表,需要Purchase表的数据补全Sold表,代码如下:

select * from(select name,'Purchase'dataType,'data'type2,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Purchase
where 1=1 /*这里加入一些限制*/ group by name
union all 
select name,'Sold'dataType,'data'type2,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Sold
where 1=1 /*这里加入一些限制*/ group by name 
union all
/*Sold伪数据*/
select name,'Sold'dataType,'pseudoData'type2,
null clothes,
null trousers,
null hat
from Purchase
where 1=1 /*这里加入一些限制*/ group by name
union all
/*Purchase伪数据*/
select name,'Purchase'dataType,'pseudoData'type2,
null clothes,
null trousers,
null hat
from Sold
where 1=1 /*这里加入一些限制*/ group by name )order by name asc,datatype desc ,type2 asc
/*1=1的限制是对该表的限制,伪表的限制应该和该table的限制一致*/

然后就很好办了,只需要group by函数就可以实现合并效果:

select name,datatype,
sum(clothes)clothes,sum(trousers)trousers,sum(hat)hat
from(select name,'Purchase'dataType,'data'type2,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Purchase
where 1=1 /*这里加入一些限制*/ group by name
union all 
select name,'Sold'dataType,'data'type2,
sum(case when type='clothes' then nvl(data_num,0) end)clothes,
sum(case when type='trousers' then nvl(data_num,0) end)trousers,
sum(case when type='hat' then nvl(data_num,0) end)hat
from Sold
where 1=1 /*这里加入一些限制*/ group by name 
union all
/*Purchase伪数据*/
select name,'Sold'dataType,'pseudoData'type2,
null clothes,
null trousers,
null hat
from Purchase
where 1=1 /*这里加入一些限制*/ group by name
union all
/*Sold伪数据*/
select name,'Purchase'dataType,'pseudoData'type2,
null clothes,
null trousers,
null hat
from Sold
where 1=1 /*这里加入一些限制*/ group by name ) 
group by name,datatype order by name asc,datatype desc

这样,小黑就有了Sold数据,小红有了Purchase数据,虽然为空,但是看起来已经好很多了,拿给别人使用,也是比较舒服的,不知道小白啰哩啰唆有没有给大家讲明白,感谢大家的支持