join 질문 0 8 629

by 딜레이쿼리 [SQL Query] [2019.11.29 11:34:37]


안녕하세요! 선배님들!

sql query를 작성하던 중 join 시 데이터가 원하는 결과가 나오지 않아 질문드립니다

member라는 테이블과

member_proc이라는 테이블을 조인하는데 아래와 같은 쿼리를 사용합니다.

 

select sm.SM_IDX, sm.SM_NM, sm.SM_CODE, sm.C_IDX, smp.md_dtm as smp3, smp4.md_dtm as smp4
from T_SC_MEMBER SM
    left join T_SC_MEMBER_PROC smp
        on sm.SM_IDX = smp.SM_IDX
            and smp.MD_YMD = '2019-11-14'
            and smp.MD_STATS = 3
    left join T_SC_MEMBER_PROC smp4
        on sm.SM_IDX = smp4.SM_IDX
            and smp4.MD_YMD = '2019-11-14'
            and smp4.MD_STATS = 4
where sm.C_IDX = 1 and sm.sm_code = '03150'

그러면 아래처럼 결과가 나오는데 중복데이터가 생성됩니다 ㅠㅠ

SM_IDX    SM_NM    SM_CODE    C_IDX    smp3        smp4
8    권엔젤    03150    1    2019-11-14 13:45:12.777    2019-11-14 14:51:48.753
8    권엔젤    03150    1    2019-11-14 13:45:12.777    2019-11-14 14:53:09.817
8    권엔젤    03150    1    2019-11-14 14:51:51.410    2019-11-14 14:51:48.753
8    권엔젤    03150    1    2019-11-14 14:51:51.410    2019-11-14 14:53:09.817
8    권엔젤    03150    1    2019-11-14 14:53:29.500    2019-11-14 14:51:48.753
8    권엔젤    03150    1    2019-11-14 14:53:29.500    2019-11-14 14:53:09.817

 

smp3만 별도로 조회를 하면 데이터는 3개, smp4만 별도로 조회하면 데이터는 4개인데

조인만하면 저렇게 나오는 이유를 모르겠습니다 ㅠㅠ

아래처럼 결과를 뽑으려면 어떻게 해야하는지 방법을 알 수 있을까요?? 도움부탁드립니다

SM_IDX    SM_NM    SM_CODE    C_IDX    smp3        smp4
8    권엔젤    03150    1    2019-11-14 13:45:12.777    2019-11-14 14:51:48.753
8    권엔젤    03150    1    2019-11-14 14:51:51.410    2019-11-14 14:53:09.817
8    권엔젤    03150    1    2019-11-14 14:53:29.500    null

 

감사합니다

딜레이쿼리 올림

by jkson [2019.11.29 11:49:14]

조건을 만족하는

smp3 데이터 3개 13:45:12.777, 14:51:51.410, 14:53:29.500

smp4 데이터 2개 14:51:48.753, 14:53:09.817

가 있으니

그냥 조인하게 되면 3 곱하기 2 = 6개의 행이 나오는 거죠.

조인 가능한 조건이 있다면 추가하시고(status 3과 4를 이어줄 수 있는..) 

조인 관계가 성립할 수 없다면 각 테이블 실제 데이터를 샘플로 올려주시면

도움이 될듯하네요.

status 3 시간 이후에 존재하는 status 4와 한 짝이 되는 것 같긴한데..


by 딜레이쿼리 [2019.11.29 11:53:05]

답변감사합니다 선배님

smp 테이블의 실 데이터는 아래와 같습니다!

SM_IDX    sm_code    c_idx    md_dtm    md_stats
8    03150    1    2019-11-14 13:45:12.777    3
8    03150    1    2019-11-14 14:51:48.753    4
8    03150    1    2019-11-14 14:51:51.410    3
8    03150    1    2019-11-14 14:53:09.817    4
8    03150    1    2019-11-14 14:53:29.500    3

 


by 마농 [2019.11.29 12:26:47]

각각의 순번을 부여하고 조인 조건으로 순번을 사용 해야 하는 문제인데요.
row_number 를 사용 가능한 DBMS 나 버전인지?
분석함수 사용 가능 하다면 쿼리가 간결해질 수 있고
그렇지 않다면 순번을 부여하기 위해 복잡한 로직이 들어가야 할 것입니다.


by 딜레이쿼리 [2019.11.29 12:32:03]

답변 감사합니다 선배님!

mssql 2016버전을 사용하고 있습니다

분석함수찾아보겠습니다


by 마농 [2019.11.29 13:34:58]
WITH t_sc_member AS
(
SELECT 8 sm_idx, '권엔젤' sm_nm, '03150' sm_code, 1 c_idx
)
, t_sc_member_proc AS
(
SELECT 8 sm_idx, '2019-11-14' md_ymd, '2019-11-14 13:45:12.777' md_dtm, 3 md_stats
UNION ALL SELECT 8, '2019-11-14', '2019-11-14 14:51:48.753', 4
UNION ALL SELECT 8, '2019-11-14', '2019-11-14 14:51:51.410', 3
UNION ALL SELECT 8, '2019-11-14', '2019-11-14 14:53:09.817', 4
UNION ALL SELECT 8, '2019-11-14', '2019-11-14 14:53:29.500', 3
)
SELECT *
  FROM (SELECT sm.sm_idx
             , sm.sm_nm
             , sm.sm_code
             , sm.c_idx
             , smp.md_stats
             , smp.md_dtm
             , ROW_NUMBER() OVER(PARTITION BY sm.sm_idx, smp.md_stats ORDER BY smp.md_dtm) rn
          FROM t_sc_member sm
          LEFT OUTER JOIN t_sc_member_proc smp
            ON sm.sm_idx = smp.sm_idx
           AND smp.md_ymd = '2019-11-14'
           AND smp.md_stats IN (3, 4)
         WHERE sm.c_idx = 1
           AND sm.sm_code = '03150'
        ) a
 PIVOT (MIN(md_dtm) FOR md_stats IN ("3", "4")) a
;

 


by 딜레이쿼리 [2019.11.29 13:52:52]

답변감사합니다 선배님!

올려주신 쿼리로 제가 원하는 정확한 쿼리가 나오는데 이게 어떻게 이렇게 나오는건지 알 수 있을까요?

구글링해서 Row_number(), Pivot을 찾아도 왜 이렇게 된건지 이해가 잘 안갑니다 ㅠ


by 마농 [2019.11.29 14:02:50]

1. ROW_NUMBER 는
 - 순번 구하는 분석함수입니다.
 - 인라인뷰 부분만 따로 실행해 보시면 쉽게 이해 가능하실 것입니다.
2. PIVOT 은
 - (행을 열로), (세로를 가로로) 바꾸는 구문입니다.
 - 쉬운 이해를 위해 다른 방식으로도 구현해 봤습니다.
 

SELECT sm_idx, sm_nm, sm_code, c_idx, rn
     , MIN(CASE md_stats WHEN 3 THEN md_dtm END) smp3 
     , MIN(CASE md_stats WHEN 4 THEN md_dtm END) smp4 
  FROM (SELECT sm.sm_idx
             , sm.sm_nm
             , sm.sm_code
             , sm.c_idx
             , smp.md_stats
             , smp.md_dtm
             , ROW_NUMBER() OVER(PARTITION BY sm.sm_idx, smp.md_stats ORDER BY smp.md_dtm) rn
          FROM t_sc_member sm
          LEFT OUTER JOIN t_sc_member_proc smp
            ON sm.sm_idx = smp.sm_idx
           AND smp.md_ymd = '2019-11-14'
           AND smp.md_stats IN (3, 4)
         WHERE sm.c_idx = 1
           AND sm.sm_code = '03150'
        ) a
 GROUP BY sm_idx, sm_nm, sm_code, c_idx, rn
;

 


by 딜레이쿼리 [2019.11.29 15:47:00]

답변감사합니다 선배님

Row_number 를 사용해 각각 구해진 데이터에 순서를 붙여서 그룹으로 묶으니 피벗을 사용한 값과 동일하게 데이터가 나오는 것 확인했습니다.

Pivot은 행을 열로, 세로를 가로로 바꾼다고 말씀하셨는데, 

PIVOT (MIN(md_dtm) FOR md_stats IN ("3", "4") <= 이 구문이 md_dtm 행을 3, 4 기준으로 열로 변경하는게 맞는건지요?? 데이터가 나오는 걸 보면 맞는거 같습니다 ㅎㅎ

기존에는 join으로만 하려고 엄청 찾아봤는데, 알려주셔서 너무 속이 시원합니다 ㅠㅠ
감사합니다!!

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