Oracle SQL:如何显示空周/周无数据? [英] Oracle SQL: How to show empty weeks / weeks without data?
本文介绍了Oracle SQL:如何显示空周/周无数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
类似于此问题,但我的数据集已一个带有多个ID的附加列。每个ID都有一个数据集,以一个固定的时间段返回,并且一些星期可能会丢失数据 - 我想填写缺少周的值。
Similar to this question, but my dataset has an additional column with many IDs. Each ID has a dataset that goes back by a constant timeframe, and some of the weeks may be missing data - I would like to fill in values for missing weeks.
例如,我想要:
ID WEEKEND_DAY VALUE
A00 2012-01-01 1
A00 2012-01-08 7
B00 2012-01-08 4
B00 2012-01-15 3
扩展到:
ID WEEKEND_DAY VALUE
A00 2012-01-01 1
A00 2012-01-08 7
A00 2012-01-15 0
B00 2012-01-01 0
B00 2012-01-08 4
B00 2012-01-15 3
我已经有一个已知的WEEKEND_DAY范围(在上述情况下,每周从2012-01-01至2012-01-15)我想填写,我该怎么做?
where I already have a known range of WEEKEND_DAYs (in the above case, weekly from 2012-01-01 to 2012-01-15) that I want to fill in. How can I go about doing this?
推荐答案
p>使用分区外连接:
select data.id, weeks.weekend_day, nvl(value, 0) value
from
(
select date '2012-01-01' weekend_day from dual union all
select date '2012-01-08' weekend_day from dual union all
select date '2012-01-15' weekend_day from dual
) weeks
left join
(
select 'A00' id, date '2012-01-01' weekend_day, 1 value from dual union all
select 'A00' id, date '2012-01-08' weekend_day, 7 value from dual union all
select 'B00' id, date '2012-01-08' weekend_day, 4 value from dual union all
select 'B00' id, date '2012-01-15' weekend_day, 3 value from dual
) data
partition by (data.id)
on weeks.weekend_day = data.weekend_day
这篇关于Oracle SQL:如何显示空周/周无数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文