Oracle SQL:如何显示空周/周无数据? [英] Oracle SQL: How to show empty weeks / weeks without data?

查看:222
本文介绍了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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆