꿈꾸는 개발자, DBA 커뮤니티 구루비

개수 누적차감!

by 가나다아아 [Oracle 기초] 누적차감 [2021.07.02 20:35:04]

두 테이블에 서로 연결되는 COL1,COL2값이 있다고했을때(한쪽(T1)을 기준으로 다른한쪽(T2)은 없을수도 있을수도있음)

T2테이블의 총 카운트로 T1테이블에 있다면 누적으로 카운트? 차감이되서 있는지 없는지 확인하는 형식의 쿼리를 짜고싶습니다 ㅠㅜ..

#T1.

COL1 COL2

01 2

02 3

02 6

03 4

04 5

#T2

COL1 COL2

01 2

01 4

02 3

02 6

#. RESULT

COL1 COL2 FLAG

01 2 O

01 2 X

02 3 O

02 3 X

02 6 O

03 4 X

04 5 X

by pajama [2021.07.02 21:10:35]

결과는 맞는 것 같은데..참고 되시면 좋겠네요~ with절 말고 아래 쿼리만 보시면 됩니다~

with t1 (col1, col2) as (
select '01', 2 from dual union all
select '01', 2 from dual union all
select '02', 3 from dual union all
select '02', 3 from dual union all
select '02', 6 from dual union all
select '03', 4 from dual union all
select '04', 5 from dual
),
t2 (col1, col2) as (
select '01', 2 from dual union all
select '01', 4 from dual union all
select '02', 3 from dual union all
select '02', 6 from dual
)
select col1, col2, case when rn = 2 or t2col2 is null then 'X' else 'O' end flag 
from (
select t1.col1 col1, t1.col2 col2, row_number() over (partition by t1.col1, t1.col2 order by t1.col1) rn, t2.col2 t2col2
from t1 left outer join t2 on t1.col1=t2.col1 and t1.col2=t2.col2
)

by 가나다아아 [2021.07.02 21:18:20]

답변감사합니다!

T2테이블에 '01',2 하나를 더 추가하게되면

T1테이블에 '01',2 가 2개 이므로

T1테이블 기준 '01',2의 FLAG가 둘다 O로 나와야하는데

'01',2 로우가 4개로 늘어나네요..ㅠㅜ

by pajama [2021.07.02 22:58:23]

중복을 제거하면 되지 않을까요?

select col1, col2, case when rn = 2 or t2col2 is null then 'X' else 'O' end flag 
from (
select t1.col1 col1, t1.col2 col2, row_number() over (partition by t1.col1, t1.col2 order by t1.col1) rn, t2.col2 t2col2
from t1 left outer join (select distinct col1, col2 from t2) t2 on t1.col1=t2.col1 and t1.col2=t2.col2
)

by 뉴비디비 [2021.07.03 00:25:24]

WITH T1 (COL1, COL2 ) AS (
    SELECT '01', 2 FROM DUAL UNION ALL
    SELECT '01', 2 FROM DUAL UNION ALL
    SELECT '02', 3 FROM DUAL UNION ALL
    SELECT '02', 3 FROM DUAL UNION ALL
    SELECT '02', 6 FROM DUAL UNION ALL
    SELECT '03', 4 FROM DUAL UNION ALL 
    SELECT '04', 5 FROM DUAL 
), T2 (COL1, COL2 ) AS (
    SELECT '01', 2 FROM DUAL UNION ALL
    SELECT '01', 4 FROM DUAL UNION ALL
    SELECT '02', 3 FROM DUAL UNION ALL
    SELECT '02', 6 FROM DUAL 
)
-- 이런 의도 인거 같아요.
SELECT
    TT1.COL1, TT1.COL2, DECODE(TT2.RN1,NULL,'X','0') AS FLAG
FROM (
    SELECT COL1, COL2, ROW_NUMBER() OVER(PARTITION BY COL1, COL2 ORDER BY COL1, COL2) AS RN1 FROM T1 
) TT1 LEFT JOIN (
    SELECT COL1, COL2, ROW_NUMBER() OVER(PARTITION BY COL1, COL2 ORDER BY COL1, COL2) AS RN1 FROM T2 
) TT2 
    ON TT1.COL1 = TT2.COL1 AND TT1.COL2 = TT2.COL2 AND TT1.RN1 = TT2.RN1 
ORDER BY 1, 2

by 마농 [2021.07.06 10:14:05]

WITH t1 AS
(
SELECT '01' col1, 2 col2 FROM dual
UNION ALL SELECT '01', 2 FROM dual
UNION ALL SELECT '02', 3 FROM dual
UNION ALL SELECT '02', 3 FROM dual
UNION ALL SELECT '02', 6 FROM dual
UNION ALL SELECT '03', 4 FROM dual
UNION ALL SELECT '04', 5 FROM dual
)
, t2 AS
(
SELECT '01' col1, 2 col2 FROM dual
UNION ALL SELECT '01', 4 FROM dual
UNION ALL SELECT '02', 3 FROM dual
UNION ALL SELECT '02', 6 FROM dual
)
SELECT a.col1
     , a.col2
     , NVL2(b.rn, 'O', 'X') flag
  FROM (SELECT col1, col2
             , ROW_NUMBER() OVER(PARTITION BY col1, col2 ORDER BY 1) rn
          FROM t1
        ) a
  LEFT OUTER JOIN
       (SELECT col1, col2
             , ROW_NUMBER() OVER(PARTITION BY col1, col2 ORDER BY 1) rn
          FROM t2
        ) b
    ON a.col1 = b.col1
   AND a.col2 = b.col2
   AND a.rn   = b.rn
 ORDER BY a.col1, a.col2, a.rn
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변