17,082
社区成员
发帖
与我相关
我的任务
分享
create table TEST
(
ID VARCHAR2(15),
HOUR NUMBER,
VALUE1 NUMBER,
TIME DATE,
VALUE2 NUMBER,
NAME NVARCHAR2(15)
)
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('a', 1, 1, to_date('17-10-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'aa');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('a', 2, 1, to_date('17-10-2018 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'aa');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('a', 4, 1, to_date('17-10-2018 04:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'aa');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('b', 1, 1, to_date('17-10-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'bb');
insert into TEST (ID, HOUR, VALUE1, TIME, VALUE2, NAME)
values ('c', 2, 1, to_date('17-10-2018 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), 1, 'cc');
commit;
select distinct a.id,a.hour,nvl(b.value1,0) as value1,a.time,nvl(b.value2,0) as value2,a.name
from(select id,rn as hour,trunc(time)+rn/24 as time,name
from test,(select rownum as rn from dual connect by rownum <= 24)
)a left join test b on a.id = b.id and a.time = b.time
order by a.id,a.hour
select t1.id,
nvl(t1.hour, t2.hour) hour,
nvl(t1.value1, 0) value1,
nvl(t1.time, t2.time) time,
nvl(t1.value2, 0) value2,
max(t1.name) over(partition by t1.id) name
from test t1 partition by(t1.id)
right join (select level hour,
to_date('20181017', 'yyyymmdd') + level / 24 time
from dual
connect by level <= 4) t2 on t1.hour = t2.hour;
with t1 as (select distinct hour from test)
select a,id,t1.hour,nvl(a.value1,0),a.time,nvl(a.value2,0),a.name from test a
PARTITION BY (a.id)
right join t1
on t1.hour=a.hour