데이터베이스 질문과 답변

쿼리질문 0 6 1,088

by 띵똥 [SQL Query] [2017.11.22 10:12:40]

이름  / 구분 / 일자

황형호 01 20121001
이정훈 01 20140408
배준희 01 20140619
황형호 02 20140619
이정훈 02 20140619
황형호 01 20140804
배준희 02 20140717
김동빈 01 20141101
황형호 02 20141101
배준희 01 20150201
김동빈 02 20150131
황형호 01 20150401
배준희 02 20150401
배준희 01 20150701
황형호 02 20150630
이강재 01 20160405
배준희 02 20160405
배준희 01 20160922
이강재 02 20160922
황형호 01 20170101
배준희 02 20170103

 

위 구분은 01=선임/02=해임 입니다. 해당 테이블에서 이름/선임일자/해임일자 이렇게 나오게 하려면 어떻게 해야되나요..

ex) 황형호 20121001 20140619

     황형호 20140804 20141101 ...

by 가을에사랑 [2017.11.22 11:49:44] 
아마 좀 더 좋은 쿼리를 다른 분이 알려주시겠지만 나름대로 올려봤습니다.

SELECT
        CHARGE_NAME, MAX(S1) S1, MAX(S2) S2
FROM
(
    SELECT
        CHARGE_NAME, DECODE(CHARGE_CODE, '01', CHARGE_DATE, '') S1, DECODE(CHARGE_CODE, '02', CHARGE_DATE, '') S2
    FROM
    (
        SELECT '황형호' CHARGE_NAME, '01' CHARGE_CODE, '20121001' CHARGE_DATE FROM DUAL
        UNION ALL
        SELECT '황형호', '02', '20121002' FROM DUAL
        UNION ALL
        SELECT '홍길동', '01', '20121001' FROM DUAL
        UNION ALL
        SELECT '홍길동', '02', '20121003' FROM DUAL
    )
)
GROUP BY CHARGE_NAME

 

by 띵똥 [2017.11.22 15:15:00]

감사합니다만 그렇게 할경우 ... 황형호  20170101 20150630 이렇게 나오네요.....

by 가을에사랑 [2017.11.22 15:41:22]

아...한 사람당 여러번 선임/해임되는걸 못봤네요 죄송요 ㅠㅠ

그럼 순차적으로 선임/해임으로 나오면 되는건가요?

by 가을에사랑 [2017.11.22 15:49:02] 
SELECT
        CHARGE_NAME, MAX(S1) S1, MAX(S2) S2
FROM
(
    SELECT
        MOK, CHARGE_NAME, DECODE(CHARGE_CODE, '01', CHARGE_DATE, '') S1, DECODE(CHARGE_CODE, '02', CHARGE_DATE, '') S2
    FROM
    (
        SELECT
            FLOOR((ROW_NUMBER() OVER (PARTITION BY CHARGE_NAME ORDER BY CHARGE_NAME, CHARGE_DATE)+1) / 2) MOK,
            A.*
        FROM
        (
            SELECT '황형호' CHARGE_NAME, '01' CHARGE_CODE, '20121001' CHARGE_DATE FROM DUAL
            UNION ALL
            SELECT '이정훈', '01', '20140408' FROM DUAL
            UNION ALL
            SELECT '배준희', '01', '20140619' FROM DUAL
            UNION ALL
            SELECT '황형호', '02', '20140619' FROM DUAL
            UNION ALL
            SELECT '이정훈', '02', '20140619' FROM DUAL
            UNION ALL
            SELECT '황형호', '01', '20140804' FROM DUAL
            UNION ALL
            SELECT '배준희', '02', '20140717' FROM DUAL
            UNION ALL
            SELECT '김동빈', '01', '20141101' FROM DUAL
            UNION ALL
            SELECT '황형호', '02', '20141101' FROM DUAL
            UNION ALL
            SELECT '배준희', '01', '20150201' FROM DUAL
            UNION ALL
            SELECT '김동빈', '02', '20150131' FROM DUAL
            UNION ALL
            SELECT '황형호', '01', '20150401' FROM DUAL
            UNION ALL
            SELECT '배준희', '02', '20150401' FROM DUAL
            UNION ALL
            SELECT '배준희', '01', '20150701' FROM DUAL
            UNION ALL
            SELECT '황형호', '02', '20150630' FROM DUAL
            UNION ALL
            SELECT '이강재', '01', '20160405' FROM DUAL
            UNION ALL
            SELECT '배준희', '02', '20160405' FROM DUAL
            UNION ALL
            SELECT '배준희', '01', '20160922' FROM DUAL
            UNION ALL
            SELECT '이강재', '02', '20160922' FROM DUAL
            UNION ALL
            SELECT '황형호', '01', '20170101' FROM DUAL
            UNION ALL
            SELECT '배준희', '02', '20170103' FROM DUAL
        )A
        ORDER BY CHARGE_NAME, CHARGE_DATE, CHARGE_CODE
    )
)
GROUP BY CHARGE_NAME, MOK
ORDER BY CHARGE_NAME, MOK ASC

 

by 무지버기 [2017.11.23 08:55:58] 


WITH T AS (
    SELECT '황형호' NAME, '01' CODE, '20121001' T_DATE FROM DUAL  UNION ALL
    SELECT '이정훈', '01', '20140408' FROM DUAL  UNION ALL
    SELECT '배준희', '01', '20140619' FROM DUAL  UNION ALL
    SELECT '황형호', '02', '20140619' FROM DUAL  UNION ALL
    SELECT '이정훈', '02', '20140619' FROM DUAL  UNION ALL
    SELECT '황형호', '01', '20140804' FROM DUAL  UNION ALL
    SELECT '배준희', '02', '20140717' FROM DUAL  UNION ALL
    SELECT '김동빈', '01', '20141101' FROM DUAL  UNION ALL
    SELECT '황형호', '02', '20141101' FROM DUAL  UNION ALL
    SELECT '배준희', '01', '20150201' FROM DUAL  UNION ALL
    SELECT '김동빈', '02', '20150131' FROM DUAL  UNION ALL
    SELECT '황형호', '01', '20150401' FROM DUAL  UNION ALL
    SELECT '배준희', '02', '20150401' FROM DUAL  UNION ALL
    SELECT '배준희', '01', '20150701' FROM DUAL  UNION ALL
    SELECT '황형호', '02', '20150630' FROM DUAL  UNION ALL
    SELECT '이강재', '01', '20160405' FROM DUAL  UNION ALL
    SELECT '배준희', '02', '20160405' FROM DUAL  UNION ALL
    SELECT '배준희', '01', '20160922' FROM DUAL  UNION ALL
    SELECT '이강재', '02', '20160922' FROM DUAL  UNION ALL
    SELECT '황형호', '01', '20170101' FROM DUAL  UNION ALL
    SELECT '배준희', '02', '20170103' FROM DUAL
)
SELECT
NAME,S_D,E_D
FROM (
    SELECT
    NAME, CODE, T_DATE,
    ROW_NUMBER() OVER(PARTITION BY NAME,CODE ORDER BY T_DATE) GB
    FROM T
) PIVOT(MIN(T_DATE) D FOR CODE IN ('01' S,'02' E))
ORDER BY NAME

 

by 마농 [2017.11.23 20:33:31] 
SELECT nm
     , sdt
     , edt
  FROM (SELECT nm
             , cd
             , dt sdt
             , LEAD(dt) OVER(PARTITION BY nm ORDER BY dt) edt
          FROM t
        )
 WHERE cd = '01'
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입