데이터베이스 질문과 답변

다중컬럼 값 WHERE 절 IN 또는 EXISTS 활용법 2 2 2,149

by 임문자 [CUBRID] CUBRID IN EXISTS [2021.05.04 11:49:57]

WHERE 절 IN 또는 EXISTS 활용법 문의 드립니다.

 

SELECT 'SLC017, SLC030, SLC035' ROOT_B FROM DB_ROOT; ROOT_B 컬럼의 데이터를 아래 조건처럼 구현하고 있는데요.

'SLC017, SLC030, SLC035' ROOT_B의 데이터 값은 'SLC017, SLC030, SLC035' 세개 일수도 있고 'SLC017, SLC030' 두개 일수도 있고, 'SLC017' 한개 일수도 있습니다.

IN이나 EXISTST를 이용해 컬럼값과 COUNT값을 같이 아래 처럼 추출하려고 하는데요. 현재 구문으로는 추출이 되질 않아 문의드립니다...

ROOT_B CNT 
SLC023 3
SLC008 2
SLC013 3

SELECT ROOT_A, COUNT(*)
FROM
(SELECT 'SLC023' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC023' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC023' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC008' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC008' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC013' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC013' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC013' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC015' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC015' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC016' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC016' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC012' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC012' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC012' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC030' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC030' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC030' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC035' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC035' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC035' ROOT_A FROM DB_ROOT)
WHERE 
    ROOT_A IN(SELECT 'SLC017, SLC030, SLC035' ROOT_B FROM DB_ROOT)
GROUP BY ROOT_A;
    
    
    
SELECT ROOT_A, COUNT(*)
FROM
(SELECT 'SLC023' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC023' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC023' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC008' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC008' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC013' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC013' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC013' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC015' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC015' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC016' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC016' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC012' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC012' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC012' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC017' ROOT_A FROM DB_ROOT
UNION all
SELECT 'SLC030' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC030' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC030' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC035' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC035' ROOT_A FROM DB_ROOT
UNION all 
SELECT 'SLC035' ROOT_A FROM DB_ROOT)
WHERE 
    EXISTS(SELECT 'SLC017, SLC030, SLC035' ROOT_B FROM DB_ROOT b WHERE ROOT_A = b.ROOT_B)
GROUP BY ROOT_A;

by 마농 [2021.05.04 12:59:13] 
-- CUBRID --
WITH t AS
(
SELECT 'SLC023' root_a    FROM db_root
UNION ALL SELECT 'SLC023' FROM db_root
UNION ALL SELECT 'SLC023' FROM db_root
UNION ALL SELECT 'SLC008' FROM db_root
UNION ALL SELECT 'SLC008' FROM db_root
UNION ALL SELECT 'SLC013' FROM db_root
UNION ALL SELECT 'SLC013' FROM db_root
UNION ALL SELECT 'SLC013' FROM db_root
UNION ALL SELECT 'SLC015' FROM db_root
UNION ALL SELECT 'SLC015' FROM db_root
UNION ALL SELECT 'SLC016' FROM db_root
UNION ALL SELECT 'SLC016' FROM db_root
UNION ALL SELECT 'SLC012' FROM db_root
UNION ALL SELECT 'SLC012' FROM db_root
UNION ALL SELECT 'SLC012' FROM db_root
UNION ALL SELECT 'SLC017' FROM db_root
UNION ALL SELECT 'SLC017' FROM db_root
UNION ALL SELECT 'SLC017' FROM db_root
UNION ALL SELECT 'SLC017' FROM db_root
UNION ALL SELECT 'SLC030' FROM db_root
UNION ALL SELECT 'SLC030' FROM db_root
UNION ALL SELECT 'SLC030' FROM db_root
UNION ALL SELECT 'SLC035' FROM db_root
UNION ALL SELECT 'SLC035' FROM db_root
UNION ALL SELECT 'SLC035' FROM db_root
)
SELECT root_a
     , COUNT(*) cnt
  FROM t
 WHERE INSTR('SLC017, SLC030, SLC035', root_a) > 0
 GROUP BY root_a
;

 

by 임문자 [2021.05.04 13:42:20]

오~!! 마농님 감사합니다~ 제가 cubrid 9.3버전인데 'WITH'는 10버전부터 된다고 하네요... ㅜㅠ

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