sql - Oracle:两次之间每半小时获取一次

Question

我有一些数据，两次定义了一个时间范围。

CREATE TABLE MY_TIME_TABLE
(
    MY_PK     NUMBER(10)  NOT NULL ENABLE,
    FROM_TIME DATE        NOT NULL ENABLE,
    TO_TIME   DATE        NOT NULL ENABLE
);

INSERT INTO MY_TIME_TABLE(MY_PK,FROM_TIME,TO_TIME)
VALUES(1,TO_DATE('2014-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-01-01 13:00:00', 'YYYY-MM-DD HH24:MI:SS');

INSERT INTO MY_TIME_TABLE(MY_PK,FROM_TIME,TO_TIME)
VALUES(2,TO_DATE('2014-01-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-01-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS');

INSERT INTO MY_TIME_TABLEMY_PK,(FROM_TIME,TO_TIME)
VALUES(3,TO_DATE('2014-01-03 00:30:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-01-03 03:30:00', 'YYYY-MM-DD HH24:MI:SS');

我想做的是创建一个查询，该查询将返回两次之间的所有半小时块。所以它会返回如下内容:

1, 2014-01-01 09:00:00
1, 2014-01-01 09:30:00
1, 2014-01-01 10:00:00
1, 2014-01-01 10:30:00
1, 2014-01-01 11:00:00
1, 2014-01-01 11:30:00
1, 2014-01-01 12:00:00
1, 2014-01-01 12:30:00
2, 2014-01-02 14:00:00
2, 2014-01-02 14:30:00
3, 2014-01-03 00:30:00
3, 2014-01-03 01:00:00
3, 2014-01-03 01:30:00
3, 2014-01-03 02:00:00
3, 2014-01-03 02:30:00
3, 2014-01-03 03:00:00

数据保证在一个小时或半小时开始和结束，所以我不必担心部分匹配。

我通常会尝试展示我自己做了什么来解决我的问题，但在这种情况下，我什至不知道从哪里开始。

Answer 1

您可以使用分层查询或 CTE 来完成。

SQL Fiddle

Oracle 11g R2 架构设置 :

CREATE TABLE MY_TIME_TABLE ( MY_PK, FROM_TIME, TO_TIME ) AS
SELECT  1, TO_DATE('2014-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2014-01-01 13:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
UNION ALL
SELECT  2, TO_DATE('2014-01-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2014-01-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
UNION ALL
SELECT  3, TO_DATE('2014-01-03 00:30:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2014-01-03 03:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

分层查询 :

SELECT MY_PK, FROM_TIME + (LEVEL-1) / 48
FROM MY_TIME_TABLE
CONNECT BY LEVEL <= (TO_TIME - FROM_TIME) * 48
          AND PRIOR MY_PK = MY_PK
          AND PRIOR dbms_random.value IS NOT NULL

Results :

| MY_PK |         FROM_TIME+(LEVEL-1)/48 |
|-------|--------------------------------|
|     1 | January, 01 2014 09:00:00+0000 |
|     1 | January, 01 2014 09:30:00+0000 |
|     1 | January, 01 2014 10:00:00+0000 |
|     1 | January, 01 2014 10:30:00+0000 |
|     1 | January, 01 2014 11:00:00+0000 |
|     1 | January, 01 2014 11:30:00+0000 |
|     1 | January, 01 2014 12:00:00+0000 |
|     1 | January, 01 2014 12:30:00+0000 |
|     2 | January, 02 2014 14:00:00+0000 |
|     2 | January, 02 2014 14:30:00+0000 |
|     3 | January, 03 2014 00:30:00+0000 |
|     3 | January, 03 2014 01:00:00+0000 |
|     3 | January, 03 2014 01:30:00+0000 |
|     3 | January, 03 2014 02:00:00+0000 |
|     3 | January, 03 2014 02:30:00+0000 |
|     3 | January, 03 2014 03:00:00+0000 |