时间戳

首页 > 解决方案 > 我需要以下输入数据的 Sql 查询

oracle - 我需要以下输入数据的 Sql 查询

问题描述

输入

NAME ID STR_DT END_DT
P 10 01-APR-17 04-APR-17
S 20 05-APR-17 07-APR-17
M 30 08-APR-17 10-APR-17
```

输出

NAME ID DATA
P 10 01-APR-17
P 10 02-APR-17
P 10 03-APR-17
P 10 04-APR-17
S 20 05-APR-17
S 20 06-APR-17
S 20 07-APR-17
M 30 08-APR-17
M 30 09-APR-17
M 30 10-APR-17

标签： oracle

解决方案

干得好：

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with test (name, id, str_dt, end_dt) as
  2    (select 'P', 10, date '2017-04-01', date '2017-04-04' from dual union all
  3     select 'S', 20, date '2017-04-05', date '2017-04-07' from dual union all
  4     select 'M', 30, date '2017-04-08', date '2017-04-10' from dual
  5    )
  6  select name, id, (str_dt + column_value - 1) data
  7  from test cross join table(cast(multiset(select level from dual
  8                                           connect by level <= end_dt - str_dt + 1
  9                                          ) as sys.odcinumberlist))
 10  order by id, data;

N         ID DATA
- ---------- ----------
P         10 01.04.2017
P         10 02.04.2017
P         10 03.04.2017
P         10 04.04.2017
S         20 05.04.2017
S         20 06.04.2017
S         20 07.04.2017
M         30 08.04.2017
M         30 09.04.2017
M         30 10.04.2017

10 rows selected.

SQL>

推荐阅读