안녕하세요 .
성적합산 프로그램을 만들고 싶은데..
특정 인원(심사위원)수 이상이면 연산하는것을 넣고 싶습니다.
select no,name,simsa_name,q_1,q_2,q_3
from simsa
no(응시번호),name(지원자성명),simsa_name(심사위원성명),hard_q_1(창의성),hard_q_2(정직성),hard_q_3(체계성)
조회하면 이렇게 출력이 됩니다.
응시번호 | 지원자성명 | 심사위원 | 창의성 | 정직성 | 체계성 |
1 | 홍길동 | 쯔위 | 7 | 7 | 7 |
1 | 홍길동 | 채영 | 7 | 9 | 8 |
1 | 홍길동 | 다현 | 7 | 7 | 7 |
1 | 홍길동 | 지효 | 7 | 8 | 7 |
1 | 홍길동 | 나연 | 9 | 7 | 8 |
응시번호와 지원자 성명 중복을제거하고 한줄로 출력을하게되면
SELECT no
, name
, MAX(CASE WHEN RN = 1 THEN simsa_name END )simsa_name
, MAX(CASE WHEN RN = 1 THEN hard_q_1 END ) hard_q_1
, MAX(CASE WHEN RN = 1 THEN simsa_name END )simsa_name
, MAX(CASE WHEN RN = 1 THEN hard_q_2 END ) hard_q_2
, MAX(CASE WHEN RN = 1 THEN simsa_name END )simsa_name
, MAX(CASE WHEN RN = 1 THEN hard_q_3 END ) hard_q_3
, MAX(CASE WHEN RN = 2 THEN simsa_name END ) simsa_name
, MAX(CASE WHEN RN = 2 THEN hard_q_1 END ) hard_q_11
, MAX(CASE WHEN RN = 2 THEN simsa_name END ) simsa_name
, MAX(CASE WHEN RN = 2 THEN hard_q_2 END ) hard_q_21
, MAX(CASE WHEN RN = 2 THEN simsa_name END ) simsa_name
, MAX(CASE WHEN RN = 2 THEN hard_q_3 END ) hard_q_31
, MAX(CASE WHEN RN = 3 THEN simsa_name END ) simsa_name
, MAX(CASE WHEN RN = 3 THEN hard_q_1 END ) hard_q_12
, MAX(CASE WHEN RN = 3 THEN simsa_name END ) simsa_name
, MAX(CASE WHEN RN = 3 THEN hard_q_2 END ) hard_q_22
, MAX(CASE WHEN RN = 3 THEN simsa_name END ) simsa_name
, MAX(CASE WHEN RN = 3 THEN hard_q_3 END ) hard_q_32
FROM (SELECT no,name , hard_q_1 ,hard_q_2,hard_q_3, ROW_NUMBER() OVER(PARTITION BY no ORDER BY ROWNUM) RN
FROM simsa
)
GROUP BY no, kname
;
출력물이 이렇게 나옵니다.
응시번호 | 지원자성명 | 심사위원 | 창의성 | 정직성 | 체계성 | 심사위원 | 창의성 | 정직성 | 체계성 | 심사위원 | 창의성 | 정직성 | 체계성 | 심사위원 | 창의성 | 정직성 | 체계성 | 심사위원 | 창의성 | 정직성 | 체계성 |
1 | 홍길동 | 쯔위 | 7 | 7 | 7 | 채영 | 7 | 9 | 8 | 다현 | 7 | 7 | 7 | 지효 | 7 | 8 | 7 | 나연 | 9 | 7 | 8 |
여기서 심사위원 수에 따라서 계,평균을 구하고싶은데
응시번호 | 지원자성명 | 항목 | 심사위원판정 | 계 | 평균 | 최종평균합계 | ||||
쯔위 | 채영 | 다현 | 지효 | 나연 | ||||||
1 | 홍길동 | 창의성 | 7점 | 7점 | 7점 | 7점 | 9점 | 21점 | 7점 | 7.3점 |
정직성 | 7점 | 9점 | 7점 | 8점 | 7점 | 23점 | 7.6점 | |||
체계성 | 7점 | 8점 | 7점 | 7점 | 8점 | 22점 | 7.3점 |
이렇게 심사위원수가 5명이상일경우(쯔위,채영,다현,지효,나연)에는 항목별로 최고점과 최하점을 제외하고 계(합계), 평균 --> 창의성 : 7점,7점,7점,7점,9점 이기때문에 7점(최하점),9점(최고점)제외
창의성 : 계(7+7+7)=21 평균(7+7+7)/3= 7
정직성 : 7점,9점,7점,8점,7점이기때문에 7점(최하점), 9점(최고점)제외
정직성 : 계(7+7+8)=23 평균(7+7+8)/3 = 7.6
그러나 만약 5명 미만일경우 계,평균은 최고점과,최하점을 제외하지 않고 심사위원명단에 따라서 합계와 평균
응시번호 | 지원자성명 | 항목 | 심사위원판정 | 계 | 평균 | 최종평균합계 | ||
쯔위 | 채영 | 다현 | ||||||
1 | 홍길동 | 창의성 | 7점 | 7점 | 7점 | 21점 | 7점 | 7.3점 |
정직성 | 7점 | 9점 | 7점 | 23점 | 7.6점 | |||
체계성 | 7점 | 8점 | 7점 | 22점 | 7.3점 |
심사위원수가 쯔위,채영,다현(3명) 5명미만이기때문에 최고점과,최하점을 제외하지않고
창의성 : 7+9+7 = 23(계) , 21/3 = 7(평균)
감사합니다.
WITH simsa(no, name, simsa_name, hard_q_1, hard_q_2, hard_q_3) AS ( SELECT 1, '미나', '쯔위', 7, 7, 7 FROM dual UNION ALL SELECT 1, '미나', '채영', 7, 9, 8 FROM dual UNION ALL SELECT 1, '미나', '다현', 7, 7, 7 FROM dual UNION ALL SELECT 1, '미나', '지효', 7, 8, 7 FROM dual UNION ALL SELECT 1, '미나', '나연', 9, 7, 8 FROM dual --UNION ALL SELECT 1, '미나', '정연', 9, 7, 8 FROM dual --UNION ALL SELECT 1, '미나', '사나', 9, 7, 8 FROM dual ) SELECT no , name , DECODE(gb, 'Q1', '창의성', 'Q2', '정직성', 'Q3', '체계성') gb , NVL(q1||'', nm1) q1 , NVL(q2||'', nm2) q2 , NVL(q3||'', nm3) q3 , NVL(q4||'', nm4) q4 , NVL(q5||'', nm5) q5 , NVL(q6||'', nm6) q6 , NVL(q7||'', nm7) q7 , SUM(s) s , ROUND(SUM(s) / SUM(cnt), 2) a FROM (SELECT no, name, gb , MIN(DECODE(rn, 1, nm)) nm1, MIN(DECODE(rn, 1, q)) q1 , MIN(DECODE(rn, 2, nm)) nm2, MIN(DECODE(rn, 2, q)) q2 , MIN(DECODE(rn, 3, nm)) nm3, MIN(DECODE(rn, 3, q)) q3 , MIN(DECODE(rn, 4, nm)) nm4, MIN(DECODE(rn, 4, q)) q4 , MIN(DECODE(rn, 5, nm)) nm5, MIN(DECODE(rn, 5, q)) q5 , MIN(DECODE(rn, 6, nm)) nm6, MIN(DECODE(rn, 6, q)) q6 , MIN(DECODE(rn, 7, nm)) nm7, MIN(DECODE(rn, 7, q)) q7 , SUM(q) - CASE WHEN COUNT(*) >= 5 THEN MIN(q) + MAX(q) ELSE 0 END s , COUNT(*) - CASE WHEN COUNT(*) >= 5 THEN 2 ELSE 0 END cnt FROM (SELECT no , name , simsa_name nm , hard_q_1 q1 , hard_q_2 q2 , hard_q_3 q3 , ROW_NUMBER() OVER(PARTITION BY no ORDER BY 1) rn FROM simsa ) UNPIVOT (q FOR gb IN (q1, q2, q3)) GROUP BY no, name, gb ) a GROUP BY no, name , nm1, nm2, nm3, nm4, nm5, nm6, nm7 , ROLLUP((gb, q1, q2, q3, q4, q5, q6, q7)) ORDER BY no, a.gb NULLS FIRST ;
답변 정말 정말 감사합니다..!
밑에처럼 한줄로 출력을 하고싶습니다.. 아 그리고 마지막 평균의합계는 평균의 평균이 아니고 항목별 평균의 합계입니당
응시번호 | 지원자성명 | 심사위원 | 창의성 | 심사위원 | 정직성 | 심사위원 | 체계성 | 심사위원 | 창의성 | 심사위원 | 정직성 | 심사위원 | 체계성 | 심사위원 | 창의성 | 심사위원 | 정직성 | 심사위원 | 체계성 | 심사위원 | 창의성 | 심사위원 | 정직성 | 심사위원 | 체계성 | 심사위원 | 창의성 | 심사위원 | 정직성 | 심사위원 | 체계성 | 창의성계 | 정직성계 | 체계성계 | 창의성평균 | 정직성 평균 | 체계성평균 | 평균의합계 |
1 | 미나 | 쯔위 | 7 | 쯔위 | 7 | 쯔위 | 7 | 채영 | 7 | 채영 | 9 | 채영 | 8 | 다현 | 7 | 다현 | 7 | 다현 | 7 | 지효 | 7 | 지효 | 8 | 지효 | 7 | 나연 | 9 | 나연 | 7 | 나연 | 8 | 21 | 22 | 22 | 7 | 7.33 | 7.33 | 21.66 |
감사합니다..!
심사위원 이름을 굳이 3번 표시할 필요가 없어 한번만 표시했습니다.
WITH simsa(no, name, simsa_name, hard_q_1, hard_q_2, hard_q_3) AS ( SELECT 1, '미나', '쯔위', 7, 7, 7 FROM dual UNION ALL SELECT 1, '미나', '채영', 7, 9, 8 FROM dual UNION ALL SELECT 1, '미나', '다현', 7, 7, 7 FROM dual UNION ALL SELECT 1, '미나', '지효', 7, 8, 7 FROM dual UNION ALL SELECT 1, '미나', '나연', 9, 7, 8 FROM dual --UNION ALL SELECT 1, '미나', '정연', 9, 7, 8 FROM dual --UNION ALL SELECT 1, '미나', '사나', 9, 7, 8 FROM dual ) SELECT no, name , nm_1, q1_1, q2_1, q3_1 , nm_2, q1_2, q2_2, q3_2 , nm_3, q1_3, q2_3, q3_3 , nm_4, q1_4, q2_4, q3_4 , nm_5, q1_5, q2_5, q3_5 , nm_6, q1_6, q2_6, q3_6 , nm_7, q1_7, q2_7, q3_7 , sum_q1 , sum_q2 , sum_q3 , ROUND(sum_q1 / cnt, 2) avg_q1 , ROUND(sum_q2 / cnt, 2) avg_q2 , ROUND(sum_q3 / cnt, 2) avg_q3 , ROUND(sum_q1 / cnt, 2) + ROUND(sum_q2 / cnt, 2) + ROUND(sum_q3 / cnt, 2) avg_sum FROM (SELECT no, name , MIN(DECODE(rn, 1, nm)) nm_1 , MIN(DECODE(rn, 1, q1)) q1_1 , MIN(DECODE(rn, 1, q2)) q2_1 , MIN(DECODE(rn, 1, q3)) q3_1 , MIN(DECODE(rn, 2, nm)) nm_2 , MIN(DECODE(rn, 2, q1)) q1_2 , MIN(DECODE(rn, 2, q2)) q2_2 , MIN(DECODE(rn, 2, q3)) q3_2 , MIN(DECODE(rn, 3, nm)) nm_3 , MIN(DECODE(rn, 3, q1)) q1_3 , MIN(DECODE(rn, 3, q2)) q2_3 , MIN(DECODE(rn, 3, q3)) q3_3 , MIN(DECODE(rn, 4, nm)) nm_4 , MIN(DECODE(rn, 4, q1)) q1_4 , MIN(DECODE(rn, 4, q2)) q2_4 , MIN(DECODE(rn, 4, q3)) q3_4 , MIN(DECODE(rn, 5, nm)) nm_5 , MIN(DECODE(rn, 5, q1)) q1_5 , MIN(DECODE(rn, 5, q2)) q2_5 , MIN(DECODE(rn, 5, q3)) q3_5 , MIN(DECODE(rn, 6, nm)) nm_6 , MIN(DECODE(rn, 6, q1)) q1_6 , MIN(DECODE(rn, 6, q2)) q2_6 , MIN(DECODE(rn, 6, q3)) q3_6 , MIN(DECODE(rn, 7, nm)) nm_7 , MIN(DECODE(rn, 7, q1)) q1_7 , MIN(DECODE(rn, 7, q2)) q2_7 , MIN(DECODE(rn, 7, q3)) q3_7 , SUM(q1) - CASE WHEN COUNT(*) >= 5 THEN MIN(q1) + MAX(q1) ELSE 0 END sum_q1 , SUM(q2) - CASE WHEN COUNT(*) >= 5 THEN MIN(q2) + MAX(q2) ELSE 0 END sum_q2 , SUM(q3) - CASE WHEN COUNT(*) >= 5 THEN MIN(q3) + MAX(q3) ELSE 0 END sum_q3 , COUNT(*) - CASE WHEN COUNT(*) >= 5 THEN 2 ELSE 0 END cnt FROM (SELECT no , name , simsa_name nm , hard_q_1 q1 , hard_q_2 q2 , hard_q_3 q3 , ROW_NUMBER() OVER(PARTITION BY no ORDER BY 1) rn FROM simsa ) GROUP BY no, name ) ;
숫자는 9 보다 10 이 크지만, 문자는 '9' 가 '10' 보다 더 큽니다. '2' 도 '10' 보다 크죠.
컬럼형을 바꿀 수 있으면 지금이라도 바꾸세요.
그냥 하려면 to_number 로 형변환 시켜 사용하세요.
WITH simsa(no, name, simsa_name, hard_q_1, hard_q_2, hard_q_3) AS ( SELECT 1, '미나', '쯔위', '7', '7', '7' FROM dual UNION ALL SELECT 1, '미나', '채영', '7', '9', '8' FROM dual UNION ALL SELECT 1, '미나', '다현', '7', '7', '7' FROM dual UNION ALL SELECT 1, '미나', '지효', '7', '8', '7' FROM dual UNION ALL SELECT 1, '미나', '나연', '9', '7', '8' FROM dual --UNION ALL SELECT 1, '미나', '정연', '9', '7', '8' FROM dual --UNION ALL SELECT 1, '미나', '사나', '9', '7', '8' FROM dual ) SELECT no, name , nm_1, q1_1, q2_1, q3_1 , nm_2, q1_2, q2_2, q3_2 , nm_3, q1_3, q2_3, q3_3 , nm_4, q1_4, q2_4, q3_4 , nm_5, q1_5, q2_5, q3_5 , nm_6, q1_6, q2_6, q3_6 , nm_7, q1_7, q2_7, q3_7 , sum_q1 , sum_q2 , sum_q3 , ROUND(sum_q1 / cnt, 2) avg_q1 , ROUND(sum_q2 / cnt, 2) avg_q2 , ROUND(sum_q3 / cnt, 2) avg_q3 , ROUND(sum_q1 / cnt, 2) + ROUND(sum_q2 / cnt, 2) + ROUND(sum_q3 / cnt, 2) avg_sum FROM (SELECT no, name , MIN(DECODE(rn, 1, nm)) nm_1 , MIN(DECODE(rn, 1, q1)) q1_1 , MIN(DECODE(rn, 1, q2)) q2_1 , MIN(DECODE(rn, 1, q3)) q3_1 , MIN(DECODE(rn, 2, nm)) nm_2 , MIN(DECODE(rn, 2, q1)) q1_2 , MIN(DECODE(rn, 2, q2)) q2_2 , MIN(DECODE(rn, 2, q3)) q3_2 , MIN(DECODE(rn, 3, nm)) nm_3 , MIN(DECODE(rn, 3, q1)) q1_3 , MIN(DECODE(rn, 3, q2)) q2_3 , MIN(DECODE(rn, 3, q3)) q3_3 , MIN(DECODE(rn, 4, nm)) nm_4 , MIN(DECODE(rn, 4, q1)) q1_4 , MIN(DECODE(rn, 4, q2)) q2_4 , MIN(DECODE(rn, 4, q3)) q3_4 , MIN(DECODE(rn, 5, nm)) nm_5 , MIN(DECODE(rn, 5, q1)) q1_5 , MIN(DECODE(rn, 5, q2)) q2_5 , MIN(DECODE(rn, 5, q3)) q3_5 , MIN(DECODE(rn, 6, nm)) nm_6 , MIN(DECODE(rn, 6, q1)) q1_6 , MIN(DECODE(rn, 6, q2)) q2_6 , MIN(DECODE(rn, 6, q3)) q3_6 , MIN(DECODE(rn, 7, nm)) nm_7 , MIN(DECODE(rn, 7, q1)) q1_7 , MIN(DECODE(rn, 7, q2)) q2_7 , MIN(DECODE(rn, 7, q3)) q3_7 , SUM(q1) - CASE WHEN COUNT(*) >= 5 THEN MIN(q1) + MAX(q1) ELSE 0 END sum_q1 , SUM(q2) - CASE WHEN COUNT(*) >= 5 THEN MIN(q2) + MAX(q2) ELSE 0 END sum_q2 , SUM(q3) - CASE WHEN COUNT(*) >= 5 THEN MIN(q3) + MAX(q3) ELSE 0 END sum_q3 , COUNT(*) - CASE WHEN COUNT(*) >= 5 THEN 2 ELSE 0 END cnt FROM (SELECT no , name , simsa_name nm , TO_NUMBER(hard_q_1) q1 -- 요기 , TO_NUMBER(hard_q_2) q2 -- 요기 , TO_NUMBER(hard_q_3) q3 -- 요기 , ROW_NUMBER() OVER(PARTITION BY no ORDER BY 1) rn FROM simsa ) GROUP BY no, name ) ;