关于查询日期时时间不连续的问题
前提是在oracel中：
示例：
select aaa.date_time,nvl(bbb.num,0) inNum,nvl(bbb.out_num,0) outNum from
(select to_char(aa.date_time || ' ' || bb.dat || ':00:00') as date_time from
(Select to_char(to_date('2017-11-01', 'yyyy-mm-dd') + Rownum - 1,'yyyy-mm-dd') date_time
from dual
connect by rownum < to_date('2017-11-23', 'yyyy-mm-dd') -
to_date('2017-11-01', 'yyyy-mm-dd') + 2)aa,
(select substr(to_char(to_date('2017-11-01 06:00:00', 'yyyy-mm-dd hh24:mi:ss') +
level / 24,
'yyyy-mm-dd hh24:mi:ss'),12,2) as dat
from dual
connect by level <= 13)bb
order by date_time)aaa
left join
(select (t.check_date || ' ' || t.hour || ':00:00') date_time,t.num,t.out_num from bd_ticket_check_count t where t.check_date >= '2017-11-01' and t.check_date <= '2017-11-23')bbb
on aaa.date_time = bbb.date_time
order by date_time
示例1
获得连续时间格式为yyyy-mm-dd:参数为：'2017-11-01'，'2017-11-23'，'2017-11-01'开始时间，结束时间，开始时间
Select to_char(to_date('2017-11-01', 'yyyy-mm-dd') + Rownum - 1,'yyyy-mm-dd') date_time
from dual
connect by rownum < to_date('2017-11-23', 'yyyy-mm-dd') -
to_date('2017-11-01', 'yyyy-mm-dd') + 2
示例2
获得后面的连续小时格式为hh,参数为：'2017-11-01 06:00:00'中的06为min-1，参数为13为需要获得的时间的max-min+1
select substr(to_char(to_date('2017-11-01 06:00:00', 'yyyy-mm-dd hh24:mi:ss') +
level / 24,
'yyyy-mm-dd hh24:mi:ss'),12,2) as dat
from dual
connect by level <= 13
示例3
获得连续的日期时间 yyyy-mm-dd hh:mm:ss,参数如上示例：
select to_char(aa.date_time || ' ' || bb.dat || ':00:00') as date_time from
(Select to_char(to_date('2017-11-01', 'yyyy-mm-dd') + Rownum - 1,'yyyy-mm-dd') date_time
from dual
connect by rownum < to_date('2017-11-23', 'yyyy-mm-dd') -
to_date('2017-11-01', 'yyyy-mm-dd') + 2)aa,
(select substr(to_char(to_date('2017-11-01 06:00:00', 'yyyy-mm-dd hh24:mi:ss') +
level / 24,
'yyyy-mm-dd hh24:mi:ss'),12,2) as dat
from dual
connect by level <= 13)bb
order by date_time
获取连续的年月信息：
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2017-01', 'yyyy-MM'), ROWNUM - 1),
'yyyy-MM') as monthlist
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2017-12', 'yyyy-MM'),
to_date('2017-01', 'yyyy-MM')) + 1
order by monthlist asc
1.获取日期
SELECT TO_CHAR(TO_DATE('2014-10-01', 'yyyy-MM-dd') + ROWNUM - 1, 'yyyy-MM-dd') as daylist
FROM DUAL
CONNECT BY ROWNUM <=
trunc(to_date('2015-06-01', 'yyyy-MM-dd') -
to_date('2014-10-01', 'yyyy-MM-dd')) + 1
2.获取月份
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), ROWNUM - 1),
'yyyyMM') as monthlist
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2015-06', 'yyyy-MM'),
to_date('2014-10', 'yyyy-MM')) + 1
3.获取年份
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), (ROWNUM - 1) * 12),
'yyyy') as yearlist
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2015-06', 'yyyy-MM'),
to_date('2014-10', 'yyyy-MM')) / 12 + 1
自己做过的
select cur.data_show,
nvl(cur.sum_out,0) out_num,
nvl( cur.sum_in,0) in_num,
nvl(pre.pre_sum_out,0) pre_out_num,
nvl(pre.pre_sum_in,0) pre_in_num
from (SELECT aaa.data_show data_show,
bbb.sum_in sum_in,
bbb.sum_out sum_out
from (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2017-01', 'yyyy-MM'),
ROWNUM - 1),
'yyyy-MM') as data_show
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2017-12', 'yyyy-MM'),
to_date('2017-01', 'yyyy-MM')) + 1
order by data_show asc) aaa
left join (select substr(t.check_date, 0, 7) data_show,
sum(t.num) sum_in,
sum(t.out_num) sum_out
from bd_ticket_check_count t
where substr(t.check_date, 0, 4) = '2017'
group by substr(t.check_date, 0, 7)
order by substr(t.check_date, 0, 7) asc) bbb
on aaa.data_show = bbb.data_show
order by aaa.data_show) cur,
(SELECT aaa.data_show pre_data_show,
bbb.sum_in pre_sum_in,
bbb.sum_out pre_sum_out
from (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2016-01', 'yyyy-MM'),
ROWNUM - 1),
'yyyy-MM') as data_show
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2016-12', 'yyyy-MM'),
to_date('2016-01', 'yyyy-MM')) + 1
order by data_show asc) aaa
left join (select substr(t.check_date, 0, 7) data_show,
sum(t.num) sum_in,
sum(t.out_num) sum_out
from bd_ticket_check_count t
where substr(t.check_date, 0, 4) = '2016'
group by substr(t.check_date, 0, 7)
order by substr(t.check_date, 0, 7) asc) bbb
on aaa.data_show = bbb.data_show
order by aaa.data_show) pre
where substr(pre.pre_data_show, 5, 7) = substr(cur.data_show, 5, 7)
-------
select aa.COUNT_DATE,bb.CUT_NUM today_num from （select aa.count_date from (select a.date_time||' '||b.dat||':00:00' count_date from (Select to_char(to_date(TO_CHAR(SYSDATE,'yyyy-mm-dd'), 'yyyy-mm-dd') + Rownum - 1,'yyyy-mm-dd') date_time
from dual
connect by rownum < to_date(TO_CHAR(SYSDATE,'yyyy-mm-dd'), 'yyyy-mm-dd') -
to_date(TO_CHAR(SYSDATE-1,'yyyy-mm-dd'), 'yyyy-mm-dd') + 2)a,(select substr(to_char(to_date('2017-11-01 06:00:00', 'yyyy-mm-dd hh24:mi:ss') +
level / 24,
'yyyy-mm-dd hh24:mi:ss'),12,2) as dat
from dual
connect by level <= 24)b)aa where aa.count_date>TO_CHAR(SYSDATE,'yyyy-mm-dd')||' 08:00:00'
and aa.count_date<=TO_CHAR(SYSDATE,'yyyy-mm-dd hh24')||' 18:00:00'
order by aa.count_date asc）aa
left join
(SELECT
SUBSTR( t.count_date, 0, 13 ) || ':00:00' count_date,
SUM( t.CUT_NUM ) CUT_NUM
FROM
HD_SLUC_COUNT t
WHERE
SUBSTR( t.count_date, 0, 13 ) || ':00:00' > TO_CHAR(SYSDATE-1,'yyyy-mm-dd')||' 08:00:00'
and SUBSTR( t.count_date, 0, 13 ) || ':00:00' <= TO_CHAR(SYSDATE,'yyyy-mm-dd hh24')||' 18:00:00'
GROUP BY
SUBSTR( t.count_date, 0, 13 ) || ':00:00')bb
on SUBSTR(aa.COUNT_DATE, 11, 13)=SUBSTR(bb.COUNT_DATE, 11, 13)
create or replace view v_hd_dest_jyg_24h as
select a.dat, nvl(b.cut_num, 0) as cut_num
from
(
select to_char(to_date(to_char(to_date('2019-08-19 20', 'yyyy-mm-dd hh24:mi:ss') - 1, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')+ level / 24,
'yyyy-mm-dd hh24') || ':00:00' as dat
from dual
connect by level <= 24) a
left join
(select replace(t.count_date,'2018-01','2019-07') as dat, sum(t.cut_num) as cut_num
from hd_sluc_count_dest t
where t.source_area_code = '620200'
and t.count_date >= '2018-01-17 21:00:00'
and t.count_date < '2018-01-16 20:00:00'
