데이터베이스 질문과 답변
퀴리 질문입니다.
0
3
1,081
by 라슈 [2013.07.22 09:59:02]
데이터가
ID / R_FLG01 / R_FLG02 / R_FLG03 / R_FLG04/ ............./ R_FLG60
0001 /3 /3 /3 /3 /............./3
0002 /3 /3 /3 /3 /............./3
0003 /3 /3 /3 /3 /............./2
0004 /2 /2 /1 /1 /............./0
0005 /1 /0 /0 /0 /............./0
일때
R_FLG01~60의 값이 2나 3인 ID의 겟수는 어떻게 구해야 할까요??
답변 부탁드립니다.
by 아린
[2013.07.22 10:10:03]
WITH t(id, r_flg01, r_flg02, r_flg03, r_flg04, r_flg05) AS(
SELECT '0001', 3, 3, 3, 3, 3 FROM dual UNION ALL
SELECT '0002', 3, 3, 3, 3, 3 FROM dual UNION ALL
SELECT '0003', 3, 3, 3, 3, 2 FROM dual UNION ALL
SELECT '0004', 2, 2, 1, 1, 0 FROM dual UNION ALL
SELECT '0005', 1, 0, 0, 0, 0 FROM dual
)
SELECT COUNT(*) cnt
두분 모두 감사합니다,
덕분에 ANY 랑 REGEXP_COUNT함수 알아가네요^^
FROM t
WHERE REGEXP_COUNT(r_flg01||r_flg02||r_flg03||r_flg04||r_flg05, '2') > 0
--WHERE REGEXP_COUNT(r_flg01||r_flg02||r_flg03||r_flg04||r_flg05, '3') > 0
--WHERE REGEXP_COUNT(r_flg01||r_flg02||r_flg03||r_flg04||r_flg05, '2|3') > 0
by 우리집아찌
[2013.07.22 10:11:48]
WITH T ( ID , R_FLG01 ,R_FLG02 , R_FLG03 , R_FLG04 , R_FLG60 ) AS ( SELECT '0001', 3, 3, 3, 3, 3 FROM DUAL UNION ALL SELECT '0002', 3, 3, 3, 3, 3 FROM DUAL UNION ALL SELECT '0003', 3, 3, 3, 3, 2 FROM DUAL UNION ALL SELECT '0004', 2, 2, 1, 1, 0 FROM DUAL UNION ALL SELECT '0005', 1, 0, 0, 0, 0 FROM DUAL ) SELECT COUNT(*) FROM T WHERE 2 = ANY(R_FLG01 ,R_FLG02 , R_FLG03 , R_FLG04 , R_FLG60)
by 라슈
[2013.07.22 10:24:29]
두분 모두 감사합니다,
덕분에 ANY 랑 REGEXP_COUNT함수 알아가네요^^
) 버튼을 클릭하여 작성 하시면 됩니다.