안녕하세요..
응시번호 | 지원자성명 | 심사위원판정(합,불) | 종합판정 | 비고 | ||||
홍길동 | 이순신 | 김시민 | 윤봉길 | 김구 | ||||
1 | 지원자1 | 합 | 합 | 합 | 불 | 불 | ||
2 | 지원자2 | 불 | 불 | 불 | 합 | 합 | ||
3 | 지원자3 | 합 | 불 | 합 | 불 | 합 | ||
4 | 지원자4 | 불 | 불 | 불 | 불 | 합 | ||
5 | 지원자5 | 합 | 합 | 합 | 합 | 합 | ||
6 | 지원자6 | 합 | 합 | 불 | 합 | 합 |
심사위원 3분의 2이상 합일때 종합판정이 자동으로 합으로 될수 있게 하고 있는데..
쿼리문으로 가능할까요 ㅠㅠ
성적 테이블 : report
칼럼 : 응시번호(num), 이름(kname) , 합(report_result ='A') 불 (report_result ='B')
WITH t AS ( SELECT 1 응시번호, '지원자1' 지원자성명, '홍길동' 심사위원, '합' 판정 FROM dual UNION ALL SELECT 1, '지원자1', '이순신', '합' FROM dual UNION ALL SELECT 1, '지원자1', '김시민', '합' FROM dual UNION ALL SELECT 1, '지원자1', '윤봉길', '불' FROM dual UNION ALL SELECT 1, '지원자1', '김구' , '불' FROM dual UNION ALL SELECT 2, '지원자2', '홍길동', '불' FROM dual UNION ALL SELECT 2, '지원자2', '이순신', '불' FROM dual UNION ALL SELECT 2, '지원자2', '김시민', '불' FROM dual UNION ALL SELECT 2, '지원자2', '윤봉길', '합' FROM dual UNION ALL SELECT 2, '지원자2', '김구' , '합' FROM dual ) SELECT 응시번호 , MIN(DECODE(심사위원, '홍길동', 판정)) 홍길동 , MIN(DECODE(심사위원, '이순신', 판정)) 이순신 , MIN(DECODE(심사위원, '김시민', 판정)) 김시민 , MIN(DECODE(심사위원, '윤봉길', 판정)) 윤봉길 , MIN(DECODE(심사위원, '김구' , 판정)) 김구 , CASE WHEN COUNT(DECODE(판정, '합', 1)) >= 3 THEN '합' ELSE '불' END 종합판정 FROM t GROUP BY 응시번호 ORDER BY 응시번호 ;
"5명 이하도 이상도 될 수 있다"라는 표현보다는
몇명부터 몇명까지 가능하다 식의 구체적 표현이 좋습니다. (예 : 최소 3명 ~ 최대 7명)
"5명일때 3명일때 합격처리 됩니다."라는 표현보다는
"반올림 처리하여 3명입니다. 버림처리하여 3명입니다."식의 정확한 환산기준을 제시해 주세요.
위에서도 언급했듯이 가변 컬럼은 동적쿼리로 구현해야 합니다.
정적 쿼리로 구현하려면 결과형태를 바꿔야 합니다.
컬럼명에 이름을 출력하는 대신 번호를 출력하여 7개 최대로 고정하여 표시하는 방안이 있습니다.(최대 7개 가정)
WITH t AS ( SELECT 1 응시번호, '지원자1' 지원자성명, '홍길동' 심사위원, '합' 판정 FROM dual UNION ALL SELECT 1, '지원자1', '이순신', '합' FROM dual UNION ALL SELECT 1, '지원자1', '김시민', '합' FROM dual UNION ALL SELECT 1, '지원자1', '윤봉길', '불' FROM dual UNION ALL SELECT 1, '지원자1', '김구' , '불' FROM dual UNION ALL SELECT 2, '지원자2', '홍길동', '불' FROM dual UNION ALL SELECT 2, '지원자2', '이순신', '불' FROM dual UNION ALL SELECT 2, '지원자2', '김시민', '불' FROM dual UNION ALL SELECT 2, '지원자2', '윤봉길', '합' FROM dual UNION ALL SELECT 2, '지원자2', '김구' , '합' FROM dual ) SELECT 응시번호 , 지원자성명 , MIN(DECODE(rn, 1, 판정)) 판정1 , MIN(DECODE(rn, 2, 판정)) 판정2 , MIN(DECODE(rn, 3, 판정)) 판정3 , MIN(DECODE(rn, 4, 판정)) 판정4 , MIN(DECODE(rn, 5, 판정)) 판정5 , MIN(DECODE(rn, 6, 판정)) 판정6 , MIN(DECODE(rn, 7, 판정)) 판정7 , CASE WHEN COUNT(DECODE(판정, '합', 1)) >= FLOOR(COUNT(*) * 2/3) -- 총원의 2/3 이상 (소수점 이하 버림) THEN '합' ELSE '불' END 종합판정 FROM (SELECT 응시번호, 지원자성명, 판정 , ROW_NUMBER() OVER(PARTITION BY 응시번호 ORDER BY 심사위원) rn FROM t ) GROUP BY 응시번호, 지원자성명 ORDER BY 응시번호 ;
WITH t AS ( SELECT 1 응시번호, '지원자1' 지원자성명, '홍길동' 심사위원, '합' 판정 FROM dual UNION ALL SELECT 1, '지원자1', '이순신', '합' FROM dual UNION ALL SELECT 1, '지원자1', '김시민', '합' FROM dual UNION ALL SELECT 1, '지원자1', '윤봉길', '불' FROM dual UNION ALL SELECT 1, '지원자1', '김구' , '불' FROM dual UNION ALL SELECT 2, '지원자2', '홍길동', '불' FROM dual UNION ALL SELECT 2, '지원자2', '이순신', '불' FROM dual UNION ALL SELECT 2, '지원자2', '김시민', '불' FROM dual UNION ALL SELECT 2, '지원자2', '윤봉길', '합' FROM dual UNION ALL SELECT 2, '지원자2', '김구' , '합' FROM dual ) SELECT 응시번호 , 지원자성명 , MIN(DECODE(rn, 1, 심사)) 심사1, MIN(DECODE(rn, 1, 판정)) 판정1 , MIN(DECODE(rn, 2, 심사)) 심사2, MIN(DECODE(rn, 2, 판정)) 판정2 , MIN(DECODE(rn, 3, 심사)) 심사3, MIN(DECODE(rn, 3, 판정)) 판정3 , MIN(DECODE(rn, 4, 심사)) 심사4, MIN(DECODE(rn, 4, 판정)) 판정4 , MIN(DECODE(rn, 5, 심사)) 심사5, MIN(DECODE(rn, 5, 판정)) 판정5 , MIN(DECODE(rn, 6, 심사)) 심사6, MIN(DECODE(rn, 6, 판정)) 판정6 , MIN(DECODE(rn, 7, 심사)) 심사7, MIN(DECODE(rn, 7, 판정)) 판정7 , CASE WHEN COUNT(DECODE(판정, '합', 1)) >= FLOOR(COUNT(*) * 2/3) -- 총원의 2/3 이상 (소수점 이하 버림) THEN '합' ELSE '불' END 종합판정 FROM (SELECT 응시번호, 지원자성명, 판정 , 심사위원 AS 심사 , ROW_NUMBER() OVER(PARTITION BY 응시번호 ORDER BY 심사위원) rn FROM t ) GROUP BY 응시번호, 지원자성명 ORDER BY 응시번호 ;
동적쿼리는 쿼리 영역이 아니라 프로그램 영역입니다.
답변의 정적 쿼리 문장을 프로그램에서 만들어 내는 것입니다.
심사위원 수만큼 루프 돌려가며 쿼리문 변수에 반복되는 구문을 붙여나가면 됩니다.
프로그램 언어을 뭘 쓰는지에 따라 구현하는 구문은 달라지겠죠.
다음은 PL/SQL 을 이용한 동적쿼리 작성법입니다.
http://gurubee.net/article/19612
WITH t AS
(
SELECT 1 응시번호, '지원자1' 지원자성명, '홍길동' 심사위원, '합' 판정 FROM dual
UNION ALL SELECT 1, '지원자1', '이순신', '합' FROM dual
UNION ALL SELECT 1, '지원자1', '김시민', '합' FROM dual
UNION ALL SELECT 1, '지원자1', '윤봉길', '불' FROM dual
UNION ALL SELECT 1, '지원자1', '김구' , '불' FROM dual
UNION ALL SELECT 2, '지원자2', '홍길동', '불' FROM dual
UNION ALL SELECT 2, '지원자2', '이순신', '불' FROM dual
UNION ALL SELECT 2, '지원자2', '김시민', '불' FROM dual
UNION ALL SELECT 2, '지원자2', '윤봉길', '합' FROM dual
UNION ALL SELECT 2, '지원자2', '김구' , '합' FROM dual
UNION ALL SELECT 3, '지원자3', '홍길동', '불' FROM dual
UNION ALL SELECT 3, '지원자3', '이순신', '불' FROM dual
UNION ALL SELECT 3, '지원자3', '김시민', '불' FROM dual
UNION ALL SELECT 3, '지원자3', '윤봉길', '합' FROM dual
UNION ALL SELECT 3, '지원자3', '김구' , '합' FROM dual
UNION ALL SELECT 4, '지원자4', '홍길동', '합' FROM dual
UNION ALL SELECT 4, '지원자4', '이순신', '합' FROM dual
UNION ALL SELECT 4, '지원자4', '김시민', '불' FROM dual
UNION ALL SELECT 4, '지원자4', '윤봉길', '합' FROM dual
UNION ALL SELECT 4, '지원자4', '김구' , '합' FROM dual
)
SELECT 응시번호
, 지원자성명
, MIN(DECODE(rn, 1, 심사)) 심사1, MIN(DECODE(rn, 1, 판정)) 판정1
, MIN(DECODE(rn, 2, 심사)) 심사2, MIN(DECODE(rn, 2, 판정)) 판정2
, MIN(DECODE(rn, 3, 심사)) 심사3, MIN(DECODE(rn, 3, 판정)) 판정3
, MIN(DECODE(rn, 4, 심사)) 심사4, MIN(DECODE(rn, 4, 판정)) 판정4
, MIN(DECODE(rn, 5, 심사)) 심사5, MIN(DECODE(rn, 5, 판정)) 판정5
, MIN(DECODE(rn, 6, 심사)) 심사6, MIN(DECODE(rn, 6, 판정)) 판정6
, MIN(DECODE(rn, 7, 심사)) 심사7, MIN(DECODE(rn, 7, 판정)) 판정7
, CASE WHEN COUNT(DECODE(판정, '합', 1))
>= FLOOR(COUNT(*) * 2/3) -- 총원의 2/3 이상 (소수점 이하 버림)
THEN '합' ELSE '불' END 종합판정
FROM (SELECT 응시번호, 지원자성명, 판정
, 심사위원 AS 심사
, ROW_NUMBER() OVER(PARTITION BY 응시번호 ORDER BY 심사위원) rn
FROM t
)
GROUP BY 응시번호, 지원자성명
ORDER BY 응시번호
;
실행하면
응시번호 | 지원자성명 | 심사1 | 판정1 | 심사2 | 판정2 | 심사3 | 판정3 | 심사4 | 판정4 | 심사5 | 판정5 | '' | '' | '' | '' | 종합판정 |
1 | 지원자1 | 김구 | 불 | 김시민 | 합 | 윤봉길 | 불 | 이순신 | 합 | 홍길동 | 합 | 합 | ||||
2 | 지원자2 | 김구 | 합 | 김시민 | 불 | 윤봉길 | 합 | 이순신 | 불 | 홍길동 | 불 | 불 | ||||
3 | 지원자3 | 김구 | 합 | 김시민 | 불 | 윤봉길 | 합 | 이순신 | 불 | 홍길동 | 불 | 불 | ||||
4 | 지원자4 | 김구 | 합 | 김시민 | 불 | 윤봉길 | 합 | 이순신 | 합 | 홍길동 | 합 | 합 |
이렇게 나오는데..
총집계
응시자수 | 불합격자수 | 합격자수 | |
4 | 2 | 2 |
불합격자명단
응시번호 | 지원자 성명 |
2 | 지원자2 |
3 | 지원자3 |
합격자명단
응시번호 | 지원자 성명 |
1 | 지원자1 |
4 | 지원자4 |
이렇게..
집계결과만 따로 뽑고싶습니다.. 근데 종합판정은 테이블에 insert개념이 아닌데.....가능할까요..?
저녁식사 맛있게 하십시오.
-- 총집계 -- SELECT COUNT(*) 응시자수 , COUNT(DECODE(종합판정, '불', 1)) 불합격자수 , COUNT(DECODE(종합판정, '합', 1)) 합격자수 FROM (SELECT CASE WHEN COUNT(DECODE(판정, '합', 1)) >= FLOOR(COUNT(*) * 2/3) THEN '합' ELSE '불' END 종합판정 FROM t GROUP BY 응시번호 ) ; -- 합격자명단/불합격자명단 -- SELECT * FROM (SELECT 응시번호, 지원자성명 , CASE WHEN COUNT(DECODE(판정, '합', 1)) >= FLOOR(COUNT(*) * 2/3) THEN '합' ELSE '불' END 종합판정 FROM t GROUP BY 응시번호, 지원자성명 ) WHERE 종합판정 = '불' -- 불합격자명단 -- WHERE 종합판정 = '합' -- 합격자명단 ORDER BY 응시번호 ;
답변 감사합니다..덕분에 잘 도움이 많이 되었습니다.
선생님 응용을 조금 하고 싶은데..좀 막히네요..
만약 심사위원이 5명이상일 경우 항목별 최고점과 최하점을 제외하고 계,평균을 구하고 싶습니다.
(만약 소수점이 나올경우 둘째 자리에서 반올림)
응시번호 | 지원자성명 | 항목 | 심사위원판정 | 계 | 평균 | 최종평균합계 | ||||
홍길동 | 이순신 | 김시민 | 윤봉길 | 김구 | ||||||
1 | 지원자1 | 창의성 | 7점 | 8점 | 7점 | 8점 | 9점 | 23점 | 8점 | 7점 |
독창성 | 7점 | 7점 | 9점 | 7점 | 8점 | 22점 | 7점 | |||
정직성 | 6점 | 8점 | 9점 | 7점 | 7점 | 22점 | 7점 |
SELECT person_no
, kname
, MAX(CASE WHEN RN = 1 THEN judge_name END )simsa_staff_id
, MAX(CASE WHEN RN = 1 THEN hard_q_1 END ) hard_q_1
, MAX(CASE WHEN RN = 1 THEN judge_name END )simsa_staff_id
, MAX(CASE WHEN RN = 1 THEN hard_q_2 END ) hard_q_2
, MAX(CASE WHEN RN = 1 THEN judge_name END )simsa_staff_id
, MAX(CASE WHEN RN = 1 THEN hard_q_3 END ) hard_q_3
, MAX(CASE WHEN RN = 2 THEN judge_name END ) simsa_staff_id1
, MAX(CASE WHEN RN = 2 THEN hard_q_1 END ) hard_q_11
, MAX(CASE WHEN RN = 2 THEN judge_name END ) simsa_staff_id1
, MAX(CASE WHEN RN = 2 THEN hard_q_2 END ) hard_q_21
, MAX(CASE WHEN RN = 2 THEN judge_name END ) simsa_staff_id1
, MAX(CASE WHEN RN = 2 THEN hard_q_3 END ) hard_q_31
, MAX(CASE WHEN RN = 3 THEN judge_name END ) simsa_staff_id2
, MAX(CASE WHEN RN = 3 THEN hard_q_1 END ) hard_q_12
, MAX(CASE WHEN RN = 3 THEN judge_name END ) simsa_staff_id2
, MAX(CASE WHEN RN = 3 THEN hard_q_2 END ) hard_q_22
, MAX(CASE WHEN RN = 3 THEN judge_name END ) simsa_staff_id2
, MAX(CASE WHEN RN = 3 THEN hard_q_3 END ) hard_q_32
FROM (SELECT b.person_no,staff_id , a.hard_q_1 ,a.hard_q_2,a.hard_q_3,b.part_code,b.major,b.kname ,c.judge_name,ROW_NUMBER() OVER(PARTITION BY a.person_no ORDER BY ROWNUM) RN
FROM simsa a, staff b , judge c
WHERE a.person_no = b.pno
)
GROUP BY person_no, kname
;
이렇게 실행하면 이런식으로 출력이 됩니다.
응시번호 | 지원자성명 | 심사위원 | 창의성 | 독창성 | 정직성 | 심사위원2 | 창의성 | 독창성 | 정직성 | 심사위원3 | 창의성 | 독창성 | 정직성 |
1 | 지원자1 | 홍길동 | 7 | 8 | 6 | 이순신 | 8 | 7 | 8 | 김시민 | 7 | 9 | 9 |
새로운 질문은 답글이 아닌 새글로 질문해 주세요.
질문은 결과만 올리지 마시고
될 수 있으면 원본대비 결과표로 질문해 주세요.
WITH simsa AS ( SELECT 1 person_no, 11 judge_no, 7 hard_q_1, 7 hard_q_2, 6 hard_q_3 FROM dual UNION ALL SELECT 1, 22, 8, 7, 8 FROM dual UNION ALL SELECT 1, 33, 7, 9, 9 FROM dual UNION ALL SELECT 1, 44, 8, 7, 7 FROM dual UNION ALL SELECT 1, 55, 9, 8, 7 FROM dual ) , staff AS ( SELECT 1 pno, '쯔위' kname FROM dual ) , judge AS ( SELECT 11 judge_no, '채영' judge_name FROM dual UNION ALL SELECT 22, '다현' FROM dual UNION ALL SELECT 33, '지효' FROM dual UNION ALL SELECT 44, '정연' FROM dual UNION ALL SELECT 55, '나연' FROM dual ) SELECT person_no , kname , NVL(gb, '평가자') gb , NVL2(gb, MIN(DECODE(rn, 1, v))||'', MIN(DECODE(rn, 1, nm))) v1 , NVL2(gb, MIN(DECODE(rn, 2, v))||'', MIN(DECODE(rn, 2, nm))) v2 , NVL2(gb, MIN(DECODE(rn, 3, v))||'', MIN(DECODE(rn, 3, nm))) v3 , NVL2(gb, MIN(DECODE(rn, 4, v))||'', MIN(DECODE(rn, 4, nm))) v4 , NVL2(gb, MIN(DECODE(rn, 5, v))||'', MIN(DECODE(rn, 5, nm))) v5 , NVL2(gb, (SUM(v) - MIN(v) - MAX(v)), '') v_sum , NVL2(gb, ROUND( (SUM(v) - MIN(v) - MAX(v)) / 3), '') v_avg , NVL2(gb, ROUND(SUM(SUM(v) - MIN(v) - MAX(v)) OVER(PARTITION BY person_no, GROUPING(gb)) / 9, 2), '') tot FROM (SELECT a.person_no , b.kname , ROW_NUMBER() OVER(PARTITION BY a.person_no ORDER BY a.judge_no) rn , c.judge_name nm , a.hard_q_1 q1 , a.hard_q_2 q2 , a.hard_q_3 q3 FROM simsa a , staff b , judge c WHERE a.person_no = b.pno AND a.judge_no = c.judge_no ) UNPIVOT (v FOR gb IN (q1 AS '1.창의성', q2 AS '2.독창성', q3 AS '3.정직성')) a GROUP BY person_no, kname, ROLLUP(gb) ORDER BY person_no, kname, a.gb NULLS FIRST ;