데이터베이스 질문과 답변
answer칼럼값이 아래와 같을때 각항목별로 O의 갯수를 확인하고 싶습니다.
1~5번까지의 O,X구성입니다.
1번라인 X^0^X^X^X
2번라인 X^0^X^0^0
3번라인 0^0^X^0^0
=============
output 1^3^0^2^2
by 마농
[2011.05.03 15:28:01]
WITH t AS
(
SELECT 1 no, 'X^O^X^X^X' v FROM dual
UNION ALL SELECT 2, 'X^O^X^O^O' FROM dual
UNION ALL SELECT 3, 'O^O^X^O^O' FROM dual
)
SELECT COUNT(DECODE(SUBSTR(v, 1, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 3, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 5, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 7, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 9, 1), 'O', 1)) output
FROM t
;
(
SELECT 1 no, 'X^O^X^X^X' v FROM dual
UNION ALL SELECT 2, 'X^O^X^O^O' FROM dual
UNION ALL SELECT 3, 'O^O^X^O^O' FROM dual
)
SELECT COUNT(DECODE(SUBSTR(v, 1, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 3, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 5, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 7, 1), 'O', 1))
||'^'|| COUNT(DECODE(SUBSTR(v, 9, 1), 'O', 1)) output
FROM t
;
by 知音
[2011.05.03 15:38:35]
with t as (
select 1 cd, 'X^0^X^X^X' ans from dual union all
select 2, 'X^0^X^0^0' from dual union all
select 3, '0^0^X^0^0' from dual
)
select count(case when substr(ans, 1,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 3,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 5,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 7,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 9,1) = '0' then 1 end)
from t
select 1 cd, 'X^0^X^X^X' ans from dual union all
select 2, 'X^0^X^0^0' from dual union all
select 3, '0^0^X^0^0' from dual
)
select count(case when substr(ans, 1,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 3,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 5,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 7,1) = '0' then 1 end) || '^' ||
count(case when substr(ans, 9,1) = '0' then 1 end)
from t
) 버튼을 클릭하여 작성 하시면 됩니다.