我手头上有一张温度表,每天都会有一次记录,就像下面一样
temp recordDate
35 20130901
30 20130902
32 20130905但是我3号和4号的数据没有正常录入,但是我想得到下面的结果temp recordDate
35 20130901
30 20130902
null 20130903
null 20130904
32 20130905null可以换成其他标记,sql应该怎么写呢 sql数据
temp recordDate
35 20130901
30 20130902
32 20130905但是我3号和4号的数据没有正常录入,但是我想得到下面的结果temp recordDate
35 20130901
30 20130902
null 20130903
null 20130904
32 20130905null可以换成其他标记,sql应该怎么写呢 sql数据
2.在你的程序往数据库插入数据前先检测下有没有昨天的数据,没有就插入一条昨天的数据
with ax as
(
select 35 temp,'20130901' recordDate from dual union all
select 30 temp,'20130902' recordDate from dual union all
select 32 temp,'20130905' recordDate from dual
)
select ax.temp,bx.s_date from ax
right join
(select to_char(to_date('201309', 'yyyymm') + (rownum - 1),'yyyymmdd') s_date from dual
connect by rownum <= last_day(to_date('201309', 'yyyymm')) - to_date('201309', 'yyyymm') + 1
)bx on ax.recordDate=bx.s_date; TEMP S_DATE
---------- --------
35 20130901
30 20130902
20130903
20130904
32 20130905
20130906
20130907
20130908
20130909
20130910
20130911
20130912
20130913
20130914
20130915
20130916
...
(
select 35 temp,'20130901' recordDate from dual union all
select 30 temp,'20130902' recordDate from dual union all
select 32 temp,'20130905' recordDate from dual
)
select ax.temp,bx.s_date from ax
right join
(select to_char(to_date('201309', 'yyyymm') + (rownum - 1),'yyyymmdd') s_date from dual
connect by rownum <= last_day(to_date('201309', 'yyyymm')) - to_date('201309', 'yyyymm') + 1
)bx on ax.recordDate=bx.s_date
order by 2;