안녕하세요.
SELECT
CATEGORY_NO categoryNo,
QUESTION_NO questionNo,
PARTICIPANT_CNT participantCnt,
SUM(EXAMPLE_NO_1) exampleNo1,
SUM(EXAMPLE_NO_2) exampleNo2,
SUM(EXAMPLE_NO_3) exampleNo3,
SUM(EXAMPLE_NO_4) exampleNo4,
SUM(EXAMPLE_NO_5) exampleNo5,
'RNK' questionTypeCd
FROM POLL_ANSWER_INFO PAI,
POLL_INFO PI
WHERE PAI.POLL_ID = PI.POLL_ID
AND PI.POLL_ID = '00A850BBAD51B387B581'
AND PAI.QUESTION_TYPE_CD = 'RNK'
GROUP BY CATEGORY_NO, QUESTION_NO, PARTICIPANT_CNT
ORDER BY CATEGORY_NO, QUESTION_NO
설문조사 순위 구하는 쿼리를 구현하는데요,
현재 POLL_ANSWER_INFO 테이블 안에 있는 EXAMPLE_NO_1 ~ EXAMPLE_NO_5 안에서 순위를 구해야 합니다.
순위 구하는 쿼리좀 도와주세요 ㅠㅠㅠㅠ
SELECT categoryNo , questionNo , participantCnt , exampleNo , jumsu , RANK() OVER( PARTITION BY categoryNo, questionNo, participantCnt ORDER BY jumsu DESC) rk , questionTypeCd FROM (SELECT category_no categoryNo , question_no questionNo , participant_cnt participantCnt , SUM(example_no_1) exampleNo1 , SUM(example_no_2) exampleNo2 , SUM(example_no_3) exampleNo3 , SUM(example_no_4) exampleNo4 , SUM(example_no_5) exampleNo5 , 'RNK' questionTypeCd FROM poll_answer_info pai , poll_info pi WHERE pai.poll_id = pi.poll_id AND pi.poll_id = '00A850BBAD51B387B581' AND pai.question_type_cd = 'RNK' GROUP BY category_no, question_no, participant_cnt -- ORDER BY category_no, question_no ) UNPIVOT (jumsu FOR exampleNo IN ( exampleNo1 AS 1 , exampleNo2 AS 2 , exampleNo3 AS 3 , exampleNo4 AS 4 , exampleNo5 AS 5 ) ) ;