oracle是否在时间范围内,求一个时间段是否在另一组时间段内的SQL

我有一个以前发给别人的例子，直接贴上来了。你可以按照修改。

Drop Table TEST;

CREATE TABLE TEST

(

PATIENT_ID VARCHAR2(10),

ADMISSION_DATE_TIME DATE,

DISCHARGE_DATE_TIME DATE,

CONSTRAINT PK_TEST

PRIMARY KEY

(PATIENT_ID, ADMISSION_DATE_TIME)

);

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('152209', TO_DATE('12/25/1992 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/08/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('152209', TO_DATE('04/25/1994 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/01/1994 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('152209', TO_DATE('07/01/1994 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/28/1994 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('183130', TO_DATE('12/24/1992 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/02/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('183130', TO_DATE('03/20/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/26/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('183130', TO_DATE('09/12/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/20/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('183130', TO_DATE('05/06/1999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/20/1999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('152209', TO_DATE('01/02/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/23/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEST

(PATIENT_ID, ADMISSION_DATE_TIME, DISCHARGE_DATE_TIME)

Values

('183130', TO_DATE('09/18/1993 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/20/1994 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

COMMIT;

SELECT patient_id, admission_date_time, discharge_date_time,

CASE

WHEN LEAD (admission_date_time) OVER (PARTITION BY patient_id ORDER BY admission_date_time)

BETWEEN admission_date_time

AND discharge_date_time

THEN 'Gap'

END gap_flag,

CASE

WHEN admission_date_time

BETWEEN LAG (admission_date_time) OVER (PARTITION BY patient_id ORDER BY admission_date_time)

AND LAG (discharge_date_time) OVER (PARTITION BY patient_id ORDER BY admission_date_time)

THEN 'BeGaped'

END begap_flag

FROM TEST ;

PATIENT_ID ADMISSION DISCHARGE GAP BEGAP_F

---------- --------- --------- --- -------

152209 25-DEC-92 08-JAN-93 Gap

152209 02-JAN-93 23-DEC-93 BeGaped

152209 25-APR-94 01-JUN-94

152209 01-JUL-94 28-JUL-94

183130 24-DEC-92 02-JAN-93

183130 20-MAR-93 26-MAR-93

183130 12-SEP-93 20-SEP-93 Gap

183130 18-SEP-93 20-JAN-94 BeGaped

183130 06-MAY-99 20-MAY-99