hi테이블입니다.
hapju_num 외래키이고, instu_num 외래키로 된 테이블인데요.
hapju_num 1에 instu_num이 1,3,5,7,8,9,10,12
hapju_num 2에 instu_num이 1,2,4,5,6,9,11,12
hapju_num 3에 instu_num이 2,3,4,5,7,8,9,11
이 들어있는데요 사용자가 1 , 5를 선택하면 instu_num을 비교해서 hapju_num1과 2를 찍고 , 1,3,5,7을 선택하면 hapju_num 1만 찍고 싶습니다.
사용자가 보내는 instu_num이 해당 조건이 맞으면 hapju_num을 찍고 싶은데... 이런 결과를 얻을 수 있나요?
-- 그냥 문자열로 받으신다면. WITH T ( hapju_num , instu_num ) AS ( SELECT 1,1 FROM DUAL UNION ALL SELECT 1,3 FROM DUAL UNION ALL SELECT 1,5 FROM DUAL UNION ALL SELECT 1,7 FROM DUAL UNION ALL SELECT 1,8 FROM DUAL UNION ALL SELECT 1,9 FROM DUAL UNION ALL SELECT 1,10 FROM DUAL UNION ALL SELECT 1,12 FROM DUAL UNION ALL SELECT 2,1 FROM DUAL UNION ALL SELECT 2,2 FROM DUAL UNION ALL SELECT 2,4 FROM DUAL UNION ALL SELECT 2,5 FROM DUAL UNION ALL SELECT 2,6 FROM DUAL UNION ALL SELECT 2,9 FROM DUAL UNION ALL SELECT 2,11 FROM DUAL UNION ALL SELECT 2,12 FROM DUAL UNION ALL SELECT 3,2 FROM DUAL UNION ALL SELECT 3,3 FROM DUAL UNION ALL SELECT 3,4 FROM DUAL UNION ALL SELECT 3,5 FROM DUAL UNION ALL SELECT 3,7 FROM DUAL UNION ALL SELECT 3,8 FROM DUAL UNION ALL SELECT 3,9 FROM DUAL UNION ALL SELECT 3,11 FROM DUAL ) , input_t ( instu_num , max_cnt ) AS ( SELECT REGEXP_SUBSTR('1,5','[^,]+',1,LEVEL) , MAX(LEVEL) OVER() FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT('1,5',',')+1 --<= '1,5' 로 입력받음 ) SELECT hapju_num FROM T a , input_t b WHERE A.instu_num = B.instu_num GROUP BY hapju_num HAVING COUNT(*) = MAX(max_cnt)