求一日期计算sql语句

Question

有一数据表，包含2个日期列（date类型），创建时间（createtime）、完成时间（finishtime），1个类型列（number类型），类型（uptype）。要求：
1.按类型统计前N（假定20天）天数据个数
2.完成时间超出创建时间N天的数据，在跨天的这些天仍计算个数
3.数据天无数据个数的，显示0数据表
createtime finishtime uptype
2011-01-01 01:01:01 2011-01-01 02:01:01 1
2011-01-01 02:01:01 2011-01-01 03:01:01 1
2011-01-01 02:01:01 2011-01-01 03:01:01 2
2011-01-02 01:01:01 2011-01-04 02:01:01 2
2010-12-29 01:01:01 2011-01-10 02:01:01 3查询结果
createtime uptype1 uptype2 uptype3
2010-12-29 0 0 1
2010-12-30 0 0 1
2010-12-31 0 0 1
2011-01-01 2 1 1
2011-01-02 0 1 1
2011-01-03 0 1 1
2011-01-04 0 1 1
2011-01-05 0 0 1
2011-01-06 0 0 1
2011-01-07 0 0 1
2011-01-08 0 0 1
2011-01-09 0 0 1
2011-01-10 0 0 1
2011-01-11 0 0 0
2011-01-12 0 0 0
2011-01-13 0 0 0
2011-01-14 0 0 0
2011-01-15 0 0 0
2011-01-16 0 0 0
2011-01-17 0 0 0

Answer 1

--敲了半天注释 提示我这个论坛不存在这个帖子....with tb as(
select to_date('2011-01-01 01:01:01','yyyy-mm-dd hh24:mi:ss') createtime,
       to_date('2011-01-01 02:01:01','yyyy-mm-dd hh24:mi:ss') finishtime,
       1 uptype from dual union all
select to_date('2011-01-01 02:01:01','yyyy-mm-dd hh24:mi:ss'),
       to_date('2011-01-01 03:01:01','yyyy-mm-dd hh24:mi:ss'),
       1 from dual union all
select to_date('2011-01-01 02:01:01','yyyy-mm-dd hh24:mi:ss'),
       to_date('2011-01-01 03:01:01','yyyy-mm-dd hh24:mi:ss'),
       2 from dual union all
select to_date('2011-01-02 01:01:01','yyyy-mm-dd hh24:mi:ss'),
       to_date('2011-01-04 02:01:01','yyyy-mm-dd hh24:mi:ss'),
       2 from dual union all
select to_date('2010-12-29 01:01:01','yyyy-mm-dd hh24:mi:ss'),
       to_date('2011-01-10 02:01:01','yyyy-mm-dd hh24:mi:ss'),
       3 from dual),
--以上为提供数据的语句--创建连续时间表
tt as(
select sysdate-rownum datetime from dual
connect by rownum<=sysdate-to_date('2010-12-29 01:01:01','yyyy-mm-dd hh24:mi:ss'))--建连续时间表和数据表进行左连接
select to_char(tt.datetime,'yyyy-mm-dd'),
sum(decode(tb.uptype,1,1,0)) uptype1,
sum(decode(tb.uptype,2,1,0)) uptype2,
sum(decode(tb.uptype,3,1,0)) uptype3
from tt left join tb on to_char(tt.datetime,'yyyy-mm-dd')
between to_char(tb.createtime,'yyyy-mm-dd') and to_char(tb.finishtime,'yyyy-mm-dd')
group by to_char(tt.datetime,'yyyy-mm-dd')
order by to_char(tt.datetime,'yyyy-mm-dd')TO_CHAR(TT    UPTYPE1    UPTYPE2    UPTYPE3
---------- ---------- ---------- ----------
2010-12-29          0          0          1
2010-12-30          0          0          1
2010-12-31          0          0          1
2011-01-01          2          1          1
2011-01-02          0          1          1
2011-01-03          0          1          1
2011-01-04          0          1          1
2011-01-05          0          0          1
2011-01-06          0          0          1
2011-01-07          0          0          1
2011-01-08          0          0          1
2011-01-09          0          0          1
2011-01-10          0          0          1
2011-01-11          0          0          0
2011-01-12          0          0          0
2011-01-13          0          0          0
2011-01-14          0          0          0
2011-01-15          0          0          0
2011-01-16          0          0          0

Answer 2

我用这种方式去查时间的时候为什么不是我想要的时间呢select test.*,(createtime+rownum-1) dt,trunc(finishtime)-trunc(createtime) from test
connect by rownum<=finishtime-createtime + 1
order by id,dt

Answer 3

你的createtime和finishtime都是不连续的 你这样肯定得不到连续的时间咯

Answer 4

的递归用伪造个数据表在 左连接 以及decode来做