데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

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	8295
41009	번호 질문.. [3]	김하늘	2015-11-23	8171
41008	쿼리 속도 질문좀 드릴게요... (도와주십시오 ㅜㅜ) [4]	검콤이	2015-11-20	8589
41006	쿼리좀 도와주세여.. 부탁드립니다. [2]	초보	2015-11-19	8306
41005	데이터 한줄 내려서 정렬하기 [3]	민식이	2015-11-16	8599
41004	sql connec by 문의하나 할께요. [1]	이현정	2015-11-16	8242
41002	계층별 소계(조직도)를 처리할 방법이 있을까요? [2]	김민규	2015-11-13	9015

작업시간: 0.029초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다