데이터베이스 질문과 답변
컬럼값으로 in 검색
0
3
1,421
by 손님 [2013.07.08 16:14:32]
WITH TBL_B AS
(
SELECT 11 AS sq, '어쩌구11' AS data1, '저쩌구' AS data2 FROM dual
UNION ALL SELECT 22, '어쩌구22', '저쩌구' FROM dual
UNION ALL SELECT 33, '어쩌구33', '저쩌구' FROM dual
UNION ALL SELECT 44, '어쩌구44', '저쩌구' FROM dual
UNION ALL SELECT 55, '어쩌구55', '저쩌구' FROM dual
)
select * from TBL_B where sq in (11,22,44)
를 돌리면
11 | 어쩌구11 | 저쩌구
22 | 어쩌구22 | 저쩌구
44 | 어쩌구44 | 저쩌구
로 잘 나옵니다... 하지만
WITH TBL_A AS
(
SELECT 1 AS seq, '11' AS codes FROM dual
UNION ALL SELECT 2, '22' FROM dual
UNION ALL SELECT 3, '33' FROM dual
UNION ALL SELECT 4, '44' FROM dual
UNION ALL SELECT 5, '11,33,44' FROM dual
UNION ALL SELECT 6, '22,44' FROM dual
),
TBL_B AS
(
SELECT 11 AS sq, '어쩌구11' AS data1, '저쩌구' AS data2 FROM dual
UNION ALL SELECT 22, '어쩌구22', '저쩌구' FROM dual
UNION ALL SELECT 33, '어쩌구33', '저쩌구' FROM dual
UNION ALL SELECT 44, '어쩌구44', '저쩌구' FROM dual
UNION ALL SELECT 55, '어쩌구55', '저쩌구' FROM dual
)
select * from TBL_B where sq in (select seq from TBL_A where seq=5)
처럼 특정 컬럼안의 데이터를 일반 문자로 적용해서 or검색이 되게 하는 방법이 있는지 궁금합니다.
(
SELECT 11 AS sq, '어쩌구11' AS data1, '저쩌구' AS data2 FROM dual
UNION ALL SELECT 22, '어쩌구22', '저쩌구' FROM dual
UNION ALL SELECT 33, '어쩌구33', '저쩌구' FROM dual
UNION ALL SELECT 44, '어쩌구44', '저쩌구' FROM dual
UNION ALL SELECT 55, '어쩌구55', '저쩌구' FROM dual
)
select * from TBL_B where sq in (11,22,44)
를 돌리면
11 | 어쩌구11 | 저쩌구
22 | 어쩌구22 | 저쩌구
44 | 어쩌구44 | 저쩌구
로 잘 나옵니다... 하지만
WITH TBL_A AS
(
SELECT 1 AS seq, '11' AS codes FROM dual
UNION ALL SELECT 2, '22' FROM dual
UNION ALL SELECT 3, '33' FROM dual
UNION ALL SELECT 4, '44' FROM dual
UNION ALL SELECT 5, '11,33,44' FROM dual
UNION ALL SELECT 6, '22,44' FROM dual
),
TBL_B AS
(
SELECT 11 AS sq, '어쩌구11' AS data1, '저쩌구' AS data2 FROM dual
UNION ALL SELECT 22, '어쩌구22', '저쩌구' FROM dual
UNION ALL SELECT 33, '어쩌구33', '저쩌구' FROM dual
UNION ALL SELECT 44, '어쩌구44', '저쩌구' FROM dual
UNION ALL SELECT 55, '어쩌구55', '저쩌구' FROM dual
)
select * from TBL_B where sq in (select seq from TBL_A where seq=5)
처럼 특정 컬럼안의 데이터를 일반 문자로 적용해서 or검색이 되게 하는 방법이 있는지 궁금합니다.
by 아린
[2013.07.08 16:24:19]
SELECT *
FROM tbl_b b
WHERE EXISTS (SELECT '1'
FROM tbl_a a
WHERE seq = 5
AND a.codes LIKE '%'||b.sq||'%')
by 손님
[2013.07.08 16:46:08]
다른 방법은 없나요?
예를 들어서 위와같이 간단하게 적었었지만 좀 더 상세하게 한다면
B의 data1 에서 '어쩌구' 라고 검색했을 때
A의 *의 리스트를 뿌려주고 마지막에
codes 와 sq 가 같은 값을 출력하는데
11,33,44 처럼 복수로 되어있는 경우는 집합으로 출력해주는걸 만들고 있습니다
그래서 in 검색을 하려 합니다
예를 들어서 위와같이 간단하게 적었었지만 좀 더 상세하게 한다면
B의 data1 에서 '어쩌구' 라고 검색했을 때
A의 *의 리스트를 뿌려주고 마지막에
codes 와 sq 가 같은 값을 출력하는데
11,33,44 처럼 복수로 되어있는 경우는 집합으로 출력해주는걸 만들고 있습니다
그래서 in 검색을 하려 합니다
by 용근님
[2013.07.08 17:52:37]
WITH TBL_A AS
(
SELECT 1 AS seq, '11' AS codes FROM dual
UNION ALL SELECT 2, '22' FROM dual
UNION ALL SELECT 3, '33' FROM dual
UNION ALL SELECT 4, '44' FROM dual
UNION ALL SELECT 5, '11,33,44' FROM dual
UNION ALL SELECT 6, '22,44' FROM dual
),
TBL_B AS
(
SELECT 11 AS sq, '어쩌구11' AS data1, '저쩌구' AS data2 FROM dual
UNION ALL SELECT 22, '어쩌구22', '저쩌구' FROM dual
UNION ALL SELECT 33, '어쩌구33', '저쩌구' FROM dual
UNION ALL SELECT 44, '어쩌구44', '저쩌구' FROM dual
UNION ALL SELECT 55, '어쩌구55', '저쩌구' FROM dual
)
select *
from ( select regexp_substr ( codes, '[^,]+', 1, lv ) seq
, codes
from tbl_a a
, ( select level lv from dual connect by level <= 10 )
where regexp_count( codes , ',' ) <= lv + 1
and seq = 5
) a
, tbl_b b
where a.seq = b.sq
) 버튼을 클릭하여 작성 하시면 됩니다.