{"action":"opened","issue":{"url":"https://api.github.com/repos/hnsnmn/CJ/issues/12","labels_url":"https://api.github.com/repos/hnsnmn/CJ/issues/12/labels{/name}","comments_url":"https://api.github.com/repos/hnsnmn/CJ/issues/12/comments","events_url":"https://api.github.com/repos/hnsnmn/CJ/issues/12/events","html_url":"https://github.com/hnsnmn/CJ/issues/12","id":57284652,"number":12,"title":"해찬들 된장 설문조사 중간 결과 요청","user":{"login":"hnsnmn","id":6303698,"avatar_url":"https://avatars.githubusercontent.com/u/6303698?v=3","gravatar_id":"","url":"https://api.github.com/users/hnsnmn","html_url":"https://github.com/hnsnmn","followers_url":"https://api.github.com/users/hnsnmn/followers","following_url":"https://api.github.com/users/hnsnmn/following{/other_user}","gists_url":"https://api.github.com/users/hnsnmn/gists{/gist_id}","starred_url":"https://api.github.com/users/hnsnmn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hnsnmn/subscriptions","organizations_url":"https://api.github.com/users/hnsnmn/orgs","repos_url":"https://api.github.com/users/hnsnmn/repos","events_url":"https://api.github.com/users/hnsnmn/events{/privacy}","received_events_url":"https://api.github.com/users/hnsnmn/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/hnsnmn/CJ/labels/DATA","name":"DATA","color":"ededed"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-02-11T07:26:20Z","updated_at":"2015-02-11T07:26:20Z","closed_at":null,"body":"해찬들 된장 설문조사 중간 결과 요청드립니다. \r\n\r\nID별로 1~10번 문항의 응답이 가로열로 보일 수 있도록 정리 해주세요!\r\n\r\n\r\n추가적으로 설문 문항을 중간에 한 번 업데이트 했기 때문에.. 2월2일 10시20분 (업데이트시간) 전/후로 나눠서\r\n\r\n전달 부탁드릴게요. (SHEET 분리해주세요)\r\n\r\n\r\n쿼리 \r\n\r\n1. 다중선택 답변(2)를 제외한 나머지 답변을 조회한다. \r\n\r\nSELECT MEMBER_ID\r\n\t,   MIN(CASE POSITION WHEN 1 THEN EXAMPLE_TEXT END) AS '답변1' \r\n\t--,\tMIN(CASE POSITION WHEN 2 THEN EXAMPLE_TEXT END) AS '답변2' \r\n\t,\tMIN(CASE POSITION WHEN 3 THEN EXAMPLE_TEXT END) AS '답변3' \r\n\t,\tMIN(CASE POSITION WHEN 4 THEN EXAMPLE_TEXT END) AS '답변4' \r\n\t,\tMIN(CASE POSITION WHEN 5 THEN EXAMPLE_TEXT END) AS '답변5' \r\n\t,\tMIN(CASE POSITION WHEN 6 THEN EXAMPLE_TEXT END) AS '답변6' \r\n\t,\tMIN(CASE POSITION WHEN 7 THEN ANSWER_TEXT END) AS '답변7' \r\n\t,\tMIN(CASE POSITION WHEN 8 THEN EXAMPLE_TEXT END) AS '답변8' \r\n\t,\tMIN(CASE POSITION WHEN 9 THEN EXAMPLE_TEXT END) AS '답변9' \r\n\t,\tMIN(CASE POSITION WHEN 10 THEN EXAMPLE_TEXT END) AS '답변10' \r\n\t,\tMIN(CASE POSITION WHEN 11 THEN EXAMPLE_TEXT END) AS '답변11' \r\n\t,\tMIN(CASE POSITION WHEN 12 THEN EXAMPLE_TEXT END) AS '답변12' \r\n\t,\tMIN(CASE POSITION WHEN 13 THEN EXAMPLE_TEXT END) AS '답변13' \r\n\t,\tMIN(CASE POSITION WHEN 14 THEN EXAMPLE_TEXT END) AS '답변14' \r\n\t,\tMIN(CREATED_ON) AS dt\r\n\r\nFROM (\t\r\n\t\tselect A.*, \r\n\t\t\t   T3.text AS EXAMPLE_TEXT, \r\n\t\t\t   T2.text AS ANSWER_TEXT, \t   \r\n\t\t\t   B.POSITION\r\n\t\tfrom (\r\n\t\t\tselect ENTRY_ID, MEMBER_ID, CREATED_ON, T2.POLL_ID from cjt_event_module_entries T1\r\n\t\t\tinner join  cjt_event_module T2\r\n\t\t\tON T1.module_id = T2.module_id \r\n\t\t\twhere T1.CREATED_ON between '2015-02-02 10:20:01' and '2016-02-02 10:20:00'\r\n\t\t\tand T1.MODULE_ID = 549\r\n\t\t\tand T2.POLL_ID is not null\r\n\t\t) A inner join cjt_poll_questions B ON A.POLL_ID = B.POLL_ID \r\n\t\t\tLEFT OUTER JOIN cjt_poll_answers T2 \r\n\t\t\tON B.question_id = T2.question_id \r\n\t\t\tAND T2.entry_id = A.ENTRY_ID\r\n\t\tLEFT OUTER JOIN cjt_poll_examples T3 \r\n\t\tON T2.example_id = T3.example_id \t\t\t\r\n\r\n) X \r\nGROUP BY MEMBER_ID\r\norder by dt;\r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n2. 1번에서 조회된 member_id를 IN절에 포함해서 다중답변에 대한 답변을 조회한다. \r\nselect MIN(CASE rnum WHEN 1 THEN EXAMPLE_TEXT END) AS '2번 답변1' \r\n,   MIN(CASE rnum WHEN 2 THEN EXAMPLE_TEXT END) AS '2번 답변2' \r\n,   MIN(CASE rnum WHEN 3 THEN EXAMPLE_TEXT END) AS '2번 답변3' \r\n,   MIN(CASE rnum WHEN 4 THEN EXAMPLE_TEXT END) AS '2번 답변4' \r\n,   MIN(CASE rnum WHEN 5 THEN EXAMPLE_TEXT END) AS '2번 답변5' \r\nfrom (\r\n\tselect A.*\r\n\tfrom (\r\n\t\tselect A.MEMBER_ID, \r\n\t\t\t\t\t   T3.text AS EXAMPLE_TEXT, \t\t\t\t   \r\n\t\t\t\t\t   ROW_NUMBER() over (PARTITION by A.MEMBER_ID order by A.member_id) as rnum\r\n\t\t\t\tfrom (\r\n\t\t\t\t\tselect ENTRY_ID, MEMBER_ID, CREATED_ON, T2.POLL_ID from cjt_event_module_entries T1\r\n\t\t\t\t\tinner join  cjt_event_module T2\r\n\t\t\t\t\tON T1.module_id = T2.module_id \r\n\t\t\t\t\twhere T1.MODULE_ID = 549\r\n\t\t\t\t\tand T2.POLL_ID is not null\r\n\t\t\t\t) A inner join cjt_poll_questions B ON A.POLL_ID = B.POLL_ID \r\n\t\t\t\t\tLEFT OUTER JOIN cjt_poll_answers T2 \r\n\t\t\t\t\tON B.question_id = T2.question_id \r\n\t\t\t\t\tAND T2.entry_id = A.ENTRY_ID\r\n\t\t\t\tLEFT OUTER JOIN cjt_poll_examples T3 \r\n\t\t\t\tON T2.example_id = T3.example_id \r\n\t\twhere B.POSITION = 2\r\n\t\tand A.MEMBER_ID IN\r\n\t\t('bmh9204')\r\n\t) A\r\n) X\r\ngroup by MEMBER_ID\r\n\r\n"}}