database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41006 게시물 읽기
No. 41006
쿼리좀 도와주세여.. 부탁드립니다.
작성자
초보
작성일
2015-11-19 11:23
조회수
8,302

안녕하세요.. 항상 고수님들의 유용한 지식을 잘 활용하고 있었습니다.
그런데 다음과 같은 상황은 어떻게 해결해야 하는지 아무리 이리저리 해봐도 답이 나오지 않아 이렇게 처음으로 글을 작성합니다.
부디 고수님들의 고견을 부탁드립니다.


상황은 설문조사입니다.

1. 설문1
  1-1. 설문 1-1
       1-1-1. 설문 1-1-1
       1-1-2. 설문 1-1-2
       1-1-3. 설문 1-1-3
  1-2. 설문 1-2
       1-2-1. 설문 1-2-1
       1-2-2. 설문 1-2-2
       1-2-3. 설문 1-2-3
  1-3. 설문 1-3
       1-3-1. 설문 1-3-1
       1-3-2. 설문 1-3-2
       1-3-3. 설문 1-3-3

설문 Category별 합산을 해야 하는데 도통 답이 나오지 않네요.. 부탁드리겠습니다.. 꾸벅
 

 

WITH T AS (
    SELECT 'SCI1447288621522002' AS ID, '0'                   AS PID, 8   AS POINT, 4    AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447288641517003' AS ID, '0'                   AS PID, 7.9 AS POINT, 2.37 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447288658744004' AS ID, '0'                   AS PID, 8.1 AS POINT, 1.62 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289810041005' AS ID, 'SCI1447288621522002' AS PID, 8   AS POINT, 3.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289830750006' AS ID, 'SCI1447288621522002' AS PID, 7   AS POINT, 2.1  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289855403007' AS ID, 'SCI1447288621522002' AS PID, 9   AS POINT, 2.7  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289895850008' AS ID, 'SCI1447288641517003' AS PID, 8   AS POINT, 4    AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289915992009' AS ID, 'SCI1447288641517003' AS PID, 7   AS POINT, 2.1  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289934085010' AS ID, 'SCI1447288641517003' AS PID, 9   AS POINT, 1.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289955803011' AS ID, 'SCI1447288658744004' AS PID, 8   AS POINT, 2.4  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289979461012' AS ID, 'SCI1447288658744004' AS PID, 7   AS POINT, 2.1  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447289994278013' AS ID, 'SCI1447288658744004' AS PID, 9   AS POINT, 3.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660132293001' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660181605002' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660196923003' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660218111004' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660231538005' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660249840006' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 1.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660267198007' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 1.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660296570008' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660311082009' AS ID, 'SCI1447289810041005' AS PID, 8   AS POINT, 0.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660642665010' AS ID, 'SCI1447289830750006' AS PID, 7   AS POINT, 1.05 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660659307011' AS ID, 'SCI1447289830750006' AS PID, 7   AS POINT, 1.05 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660672181012' AS ID, 'SCI1447289830750006' AS PID, 7   AS POINT, 1.05 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660691586013' AS ID, 'SCI1447289830750006' AS PID, 7   AS POINT, 1.05 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660709784014' AS ID, 'SCI1447289830750006' AS PID, 7   AS POINT, 1.4  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660723348015' AS ID, 'SCI1447289830750006' AS PID, 7   AS POINT, 1.4  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660759933016' AS ID, 'SCI1447289855403007' AS PID, 9   AS POINT, 1.8  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660791391017' AS ID, 'SCI1447289855403007' AS PID, 9   AS POINT, 2.25 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660805326018' AS ID, 'SCI1447289855403007' AS PID, 9   AS POINT, 2.25 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660833721019' AS ID, 'SCI1447289855403007' AS PID, 9   AS POINT, 2.7  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660900448020' AS ID, 'SCI1447289895850008' AS PID, 8   AS POINT, 1.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660915160021' AS ID, 'SCI1447289895850008' AS PID, 8   AS POINT, 1.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660927736022' AS ID, 'SCI1447289895850008' AS PID, 8   AS POINT, 1.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660947348023' AS ID, 'SCI1447289895850008' AS PID, 8   AS POINT, 1.2  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660963207024' AS ID, 'SCI1447289895850008' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447660977727025' AS ID, 'SCI1447289895850008' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661019157026' AS ID, 'SCI1447289915992009' AS PID, 7   AS POINT, 1.05 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661080741027' AS ID, 'SCI1447289915992009' AS PID, 7   AS POINT, 1.4  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661093402028' AS ID, 'SCI1447289915992009' AS PID, 7   AS POINT, 1.4  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661116060029' AS ID, 'SCI1447289915992009' AS PID, 7   AS POINT, 1.4  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661161632030' AS ID, 'SCI1447289915992009' AS PID, 7   AS POINT, 1.75 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661258959031' AS ID, 'SCI1447289934085010' AS PID, 9   AS POINT, 2.7  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661283683032' AS ID, 'SCI1447289934085010' AS PID, 9   AS POINT, 2.7  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661302641033' AS ID, 'SCI1447289934085010' AS PID, 9   AS POINT, 3.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661380486034' AS ID, 'SCI1447289955803011' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661394181035' AS ID, 'SCI1447289955803011' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661409716036' AS ID, 'SCI1447289955803011' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661423145037' AS ID, 'SCI1447289955803011' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661441671038' AS ID, 'SCI1447289955803011' AS PID, 8   AS POINT, 1.6  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661473102039' AS ID, 'SCI1447289979461012' AS PID, 7   AS POINT, 1.75 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661494259040' AS ID, 'SCI1447289979461012' AS PID, 7   AS POINT, 2.1  AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661513492041' AS ID, 'SCI1447289979461012' AS PID, 7   AS POINT, 3.15 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661547993042' AS ID, 'SCI1447289994278013' AS PID, 9   AS POINT, 4.05 AS SCORE FROM DUAL UNION ALL
    SELECT 'SCI1447661562130043' AS ID, 'SCI1447289994278013' AS PID, 9   AS POINT, 4.95 AS SCORE FROM DUAL
)
SELECT  T.*,
        LEVEL AS Lv...
  FROM  T
 START  WITH ...
 CONNECT BY PRIOR ...

 

원하는  결과입니다. (Lv1, Lv2, Lv3가 필요합니다.)

ID                      PID                   POINT     SCORE     Lv1     Lv2     Lv3
-------------------------------------------------------------------------------------
SCI1447288621522002     0                         8         4      1       0       0
SCI1447288641517003     0                       7.9      2.37      2       0       0
SCI1447288658744004     0                       8.1      1.62      3       0       0
SCI1447289810041005     SCI1447288621522002       8       3.2      1       1       0
SCI1447289830750006     SCI1447288621522002       7       2.1      1       2       0
SCI1447289855403007     SCI1447288621522002       9       2.7      1       3       0
SCI1447289895850008     SCI1447288641517003       8         4      2       1       0
SCI1447289915992009     SCI1447288641517003       7       2.1      2       2       0
SCI1447289934085010     SCI1447288641517003       9       1.8      2       3       0
SCI1447289955803011     SCI1447288658744004       8       2.4      3       1       0
SCI1447289979461012     SCI1447288658744004       7       2.1      3       2       0
SCI1447289994278013     SCI1447288658744004       9       3.6      3       3       0
SCI1447660132293001     SCI1447289810041005       8       0.8      1       1       1
SCI1447660181605002     SCI1447289810041005       8       0.8      1       1       2
SCI1447660196923003     SCI1447289810041005       8       0.8      1       1       3
SCI1447660218111004     SCI1447289810041005       8       0.8      1       1       4
SCI1447660231538005     SCI1447289810041005       8       0.8      1       1       5
SCI1447660249840006     SCI1447289810041005       8       1.2      1       1       6
SCI1447660267198007     SCI1447289810041005       8       1.2      1       1       7
SCI1447660296570008     SCI1447289810041005       8       0.8      1       1       8
SCI1447660311082009     SCI1447289810041005       8       0.8      1       1       9
SCI1447660642665010     SCI1447289830750006       7      1.05      1       2       1
SCI1447660659307011     SCI1447289830750006       7      1.05      1       2       2
SCI1447660672181012     SCI1447289830750006       7      1.05      1       2       3
SCI1447660691586013     SCI1447289830750006       7      1.05      1       2       4
SCI1447660709784014     SCI1447289830750006       7       1.4      1       2       5
SCI1447660723348015     SCI1447289830750006       7       1.4      1       2       6
SCI1447660759933016     SCI1447289855403007       9       1.8      1       3       1
SCI1447660791391017     SCI1447289855403007       9      2.25      1       3       2
SCI1447660805326018     SCI1447289855403007       9      2.25      1       3       3
SCI1447660833721019     SCI1447289855403007       9       2.7      1       3       4
SCI1447660900448020     SCI1447289895850008       8       1.2      2       1       1
SCI1447660915160021     SCI1447289895850008       8       1.2      2       1       2
SCI1447660927736022     SCI1447289895850008       8       1.2      2       1       3
SCI1447660947348023     SCI1447289895850008       8       1.2      2       1       4
SCI1447660963207024     SCI1447289895850008       8       1.6      2       1       5
SCI1447660977727025     SCI1447289895850008       8       1.6      2       1       6
SCI1447661019157026     SCI1447289915992009       7      1.05      2       2       1
SCI1447661080741027     SCI1447289915992009       7       1.4      2       2       2
SCI1447661093402028     SCI1447289915992009       7       1.4      2       2       3
SCI1447661116060029     SCI1447289915992009       7       1.4      2       2       4
SCI1447661161632030     SCI1447289915992009       7      1.75      2       2       5
SCI1447661258959031     SCI1447289934085010       9       2.7      2       3       1
SCI1447661283683032     SCI1447289934085010       9       2.7      2       3       2
SCI1447661302641033     SCI1447289934085010       9       3.6      2       3       3
SCI1447661380486034     SCI1447289955803011       8       1.6      3       1       1
SCI1447661394181035     SCI1447289955803011       8       1.6      3       1       2
SCI1447661409716036     SCI1447289955803011       8       1.6      3       1       3
SCI1447661423145037     SCI1447289955803011       8       1.6      3       1       4
SCI1447661441671038     SCI1447289955803011       8       1.6      3       1       5
SCI1447661473102039     SCI1447289979461012       7      1.75      3       2       1
SCI1447661494259040     SCI1447289979461012       7       2.1      3       2       2
SCI1447661513492041     SCI1447289979461012       7      3.15      3       2       3
SCI1447661547993042     SCI1447289994278013       9      4.05      3       3       1
SCI1447661562130043     SCI1447289994278013       9      4.95      3       3       2

이 글에 대한 댓글이 총 2건 있습니다.

SELECT id, pid, point, score
     , CONNECT_BY_ROOT(rn) lv1
     , DECODE(LEVEL, 1, 0, 2, rn, 3, PRIOR rn) lv2
     , DECODE(LEVEL, 3, rn, 0) lv3
--     , SUBSTR(SYS_CONNECT_BY_PATH(rn, '-'), 2) p
  FROM (SELECT id, pid, point, score
             , ROW_NUMBER() OVER(PARTITION BY pid ORDER BY id) rn
          FROM t
        )
 START WITH pid = '0'
 CONNECT BY PRIOR id = pid
-- ORDER BY LEVEL, lv1, lv2, lv3
;

마농(manon94)님이 2015-11-19 13:16에 작성한 댓글입니다.

역시.. 마농님 항상 답변해주시는 글을 보며 열심히 배우고 있습니다.

고맙습니다.

초보님이 2015-11-19 13:34에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41010구간 시작 끝 묶음 및 일련번호 부여 문의 드립니다. [2]
김미림
2015-11-23
8286
41009번호 질문.. [3]
김하늘
2015-11-23
8166
41008쿼리 속도 질문좀 드릴게요... (도와주십시오 ㅜㅜ) [4]
검콤이
2015-11-20
8586
41006쿼리좀 도와주세여.. 부탁드립니다. [2]
초보
2015-11-19
8302
41005데이터 한줄 내려서 정렬하기 [3]
민식이
2015-11-16
8595
41004sql connec by 문의하나 할께요. [1]
이현정
2015-11-16
8235
41002계층별 소계(조직도)를 처리할 방법이 있을까요? [2]
김민규
2015-11-13
9011
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.050초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다