꿈꾸는 개발자, DBA 커뮤니티 구루비

Mysql 계층적 쿼리 질문

by 초보자 [Oracle 기초] [2024.07.17 13:08:35]

SELECT
    p.id AS pod_id,
    p.title AS pod_title,
    t.id AS topic_id,
    t.message AS topic_content,
    c.total_work_hour
FROM topic AS t
JOIN (
    SELECT topic_id, SUM(work_hour) AS total_work_hour
    FROM comment
    WHERE commentator = '이메일'
      AND work_hour > 0
      AND complete = 1
      AND is_delete = 0
      AND update_date BETWEEN '20240101' AND '20241201'
    GROUP BY topic_id
) AS c ON t.id = c.topic_id
JOIN message_board AS p ON p.id = t.message_board_id
WHERE t.complete = 1
  AND t.delete_status = 2
  AND p.status = 2

UNION ALL

SELECT
    p.id AS pod_id,
    p.title AS pod_title,
    NULL AS topic_id,
    NULL AS topic_content,
    SUM(c.total_work_hour) AS total_work_hour
FROM topic AS t
JOIN (
    SELECT topic_id, SUM(work_hour) AS total_work_hour
    FROM comment
    WHERE commentator = '이메일'
      AND work_hour > 0
      AND complete = 1
      AND is_delete = 0
      AND update_date BETWEEN '20240101' AND '20241201'
    GROUP BY topic_id
) AS c ON t.id = c.topic_id
JOIN message_board AS p ON p.id = t.message_board_id
WHERE t.complete = 1
  AND t.delete_status = 2
  AND p.status = 2
GROUP BY p.id, p.title
ORDER BY pod_title, total_work_hour desc, topic_id;

의 결과는

pod_id pod_title topic_id topic_content total_work_hour

2042	MH 테스트			4
2042	MH 테스트	312043	테스트	4
2045	프로젝트 파드 생성 테스트			30
2045	프로젝트 파드 생성 테스트	311953	linked	10
2045	프로젝트 파드 생성 테스트	311969	test	9
2045	프로젝트 파드 생성 테스트	311949	ee	4
2045	프로젝트 파드 생성 테스트	311966	dahee.lee 전체공개 토픽을 장성함 태그는 dahee.lee2 지정	3
2045	프로젝트 파드 생성 테스트	311982	안녕하세요	2
2045	프로젝트 파드 생성 테스트	311995	M-H 테스트를 하겠습니다.	2

가 나옵니다. 제가 원하는 결과는 아래와 같이 total_work_hour 가 내림차순으로 정렬되길 원합니다..

pod_id pod_title topic_id topic_content total_work_hour

2045	프로젝트 파드 생성 테스트			30
2045	프로젝트 파드 생성 테스트	311953	linked	10
2045	프로젝트 파드 생성 테스트	311969	test	9
2045	프로젝트 파드 생성 테스트	311949	ee	4
2045	프로젝트 파드 생성 테스트	311966	dahee.lee 전체공개 토픽을 장성함 태그는 dahee.lee2 지정	3
2045	프로젝트 파드 생성 테스트	311982	안녕하세요	2
2045	프로젝트 파드 생성 테스트	311995	M-H 테스트를 하겠습니다.	2

2042	MH 테스트			4
2042	MH 테스트	312043	테스트	4

by 마농 [2024.07.19 14:30:25]

WITH message_board AS
(
SELECT 2042 id, 'MH 테스트' title, 2 status
UNION ALL SELECT 2045, '프로젝트 파드 생성 테스트', 2
)
, topic AS
(
SELECT 2042 message_board_id, 312043 id, '테스트' message, 1 complete, 2 delete_status
UNION ALL SELECT 2045, 311953, 'linked', 1, 2
UNION ALL SELECT 2045, 311969, 'test', 1, 2
UNION ALL SELECT 2045, 311949, 'ee', 1, 2
UNION ALL SELECT 2045, 311966, 'dahee.lee 전체공개 토픽을 장성함 태그는 dahee.lee2 지정', 1, 2
UNION ALL SELECT 2045, 311982, '안녕하세요', 1, 2
UNION ALL SELECT 2045, 311995, 'M-H 테스트를 하겠습니다.', 1, 2
)
, comment AS
(
SELECT 312043 topic_id, 4 work_hour, '20240101' update_date, '이메일' commentator, 1 complete, 0 is_delete
UNION ALL SELECT 311953, 10, '20240101', '이메일', 1, 0
UNION ALL SELECT 311969,  9, '20240101', '이메일', 1, 0
UNION ALL SELECT 311949,  4, '20240101', '이메일', 1, 0
UNION ALL SELECT 311966,  3, '20240101', '이메일', 1, 0
UNION ALL SELECT 311982,  2, '20240101', '이메일', 1, 0
UNION ALL SELECT 311995,  2, '20240101', '이메일', 1, 0
)
SELECT pod_id
     , pod_title
     , topic_id
     , CASE WHEN topic_id IS NOT NULL THEN topic_content END topic_content
     , total_work_hour
  FROM (SELECT p.id      AS pod_id
             , p.title   AS pod_title
             , t.id      AS topic_id
             , t.message AS topic_content
             , SUM(c.work_hour) AS total_work_hour
          FROM topic t
          JOIN comment c
            ON t.id = c.topic_id
          JOIN message_board p
            ON p.id = t.message_board_id
         WHERE t.complete = 1
           AND t.delete_status = 2
           AND p.status = 2
           AND c.commentator = '이메일'
           AND c.work_hour > 0
           AND c.complete = 1
           AND c.is_delete = 0
           AND c.update_date BETWEEN '20240101' AND '20241201'
         GROUP BY p.id, t.id
          WITH ROLLUP
        HAVING p.id IS NOT NULL
        ) a
 ORDER BY SUM(total_work_hour) OVER(PARTITION BY pod_id) DESC
     , pod_id
     , total_work_hour DESC
     , topic_id
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변