--- 질문이 잘못되어 수정을 좀 했습니다. 죄송합니다.
머리가 굳었는지 아님 원래 이런건지 미숙자 도움을 구합니다.
WITH t AS
(
SELECT 'XXX' AS ITEM, 'SEOUL' AS AREA,'RECEV' AS EVENT ,'20120101' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'SEOUL' AS AREA,'SEND' AS EVENT ,'20120102' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'BUSAN' AS AREA,'ARRIV' AS EVENT ,'20120103' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'BUSAN' AS AREA,'SEND' AS EVENT ,'20120104' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'JEJU' AS AREA,'ARRIV' AS EVENT ,'20120105' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'JEJU' AS AREA,'RETURN' AS EVENT ,'20120106' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'JEJU' AS AREA,'SEND' AS EVENT ,'20120107' AS TIME FROM DUAL UNION ALL
SELECT 'XXX' AS ITEM, 'BUSAN' AS AREA,'ARRIV' AS EVENT ,'20120108' AS TIME FROM DUAL UNION ALL
SELECT 'BBB' AS ITEM, 'SEOUL' AS AREA,'SEND' AS EVENT ,'20120102' AS TIME FROM DUAL UNION ALL
SELECT 'BBB' AS ITEM, 'BUSAN' AS AREA,'ARRIV' AS EVENT ,'20120103' AS TIME FROM DUAL UNION ALL
SELECT 'CCC' AS ITEM, 'SEOUL' AS AREA,'SEND' AS EVENT ,'20120101' AS TIME FROM DUAL UNION ALL
SELECT 'CCC' AS ITEM, 'BUSAN' AS AREA,'ARRIV' AS EVENT ,'20120102' AS TIME FROM DUAL UNION ALL
SELECT 'CCC' AS ITEM, 'BUSAN' AS AREA,'SEND' AS EVENT ,'20120103' AS TIME FROM DUAL UNION ALL
SELECT 'CCC' AS ITEM, 'JEJU' AS AREA,'ARRIV' AS EVENT ,'20120104' AS TIME FROM DUAL UNION ALL
SELECT 'CCC' AS ITEM, 'JEJU' AS AREA,'DELIV' AS EVENT ,'20120105' AS TIME FROM DUAL
)
위와 같은 데이터가 있습니다. 질문은 두가지 인데요
Q1.ITEM 중에 BUSAN에 ARRIV 해서 다음 작업이 없는 데이터를 추출해야 하는데 데이터를 보면 XXX와 BBB는 둘다 부산에 ARRIV해서 다음 작업이 일어나지 않았습니다.
결과는
XXX BUSAN ARRIV 20120108
BBB BUSAN ARRIV 20120103
두가지가 나와야 합니니다.
Q2.EVENT중에 RECEV 값이 없는 데이터중 시간이 제일 빠른것 결과는
BBB SEOUL SEND 20120102
CCC SEOUL SEND 20120101 두가지가 나와야 합니다.
뭔가 쉬운듯 하면서도 안풀리네요.
내심 어려운 쿼리길 간절히 빌어봅니다. |