데이터베이스 질문과 답변

MSSQL WITH절 질문 드립니다 !ㅜ 0 1 4,047

by 튼실맨 [SQL Query] MSSQL [2024.03.15 14:33:48]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
WITH CATEGORY_TREE AS (
        SELECT
        CONVERT(VARCHAR,A1.CATEGORY_NAME) AS CATEGORY_DEPTH ,
        A1.CATEGORY_SEQ ,
        A1.CATEGORY_ORDER ,
        A1.CATEGORY_USE ,
        A1.UP_CATEGORY_SEQ ,
        A1.CATEGORY_NAME ,
        A1.CATEGORY_NAME AS D1 ,
        A1.CATEGORY_NAME AS D2
        FROM
        CATEGORY A1 WITH(NOLOCK)
        WHERE
        A1.UP_CATEGORY_SEQ = '1'
        UNION ALL
        SELECT
        CONVERT(VARCHAR,CONCAT(CT.CATEGORY_NAME, '>', A2.CATEGORY_NAME)) AS CATEGORY_DEPTH ,
        A2.CATEGORY_SEQ ,
        A2.CATEGORY_ORDER ,
        A2.CATEGORY_USE ,
        A2.UP_CATEGORY_SEQ ,
        A2.CATEGORY_NAME ,
        CT.D1 AS D1 ,
        A2.CATEGORY_NAME AS D2
        FROM
        CATEGORY A2 WITH(NOLOCK)
        INNER JOIN CATEGORY_TREE CT ON
        CT.CATEGORY_SEQ = A2.UP_CATEGORY_SEQ
        )
        SELECT
        m1.UPLOAD_TYPE AS UPLOAD_TYPE,
        m1.CONTENTS_ID AS CONTENTS_ID,
        m1.CATEGORY_CD AS CATEGORY_CD,
        CASE
        WHEN m2.CATEGORY_DEPTH IS NULL THEN '-'
        ELSE m2.CATEGORY_DEPTH
        END AS CATEGORY_DEPTH,
        m1.CONTENTS_TITLE AS CONTENTS_TITLE,
        m1.CORPORATE,
        (SELECT COUNT(P.CONTENTS_ID) FROM SCRAP P WITH(NOLOCK) WHERE P.CONTENTS_ID = m1.CONTENTS_ID
        <include refid="evtType_scrap"/>
        ) AS SCRAP_CNT,
        RANK() OVER(ORDER BY (SELECT COUNT(P.CONTENTS_ID) FROM SCRAP P WITH(NOLOCK) WHERE P.CONTENTS_ID = m1.CONTENTS_ID
        <include refid="evtType_scrap"/>
        ) DESC) AS RANK,
        (SELECT MAX(P.SCRAP_DATE) FROM SCRAP P WITH(NOLOCK) WHERE P.CONTENTS_ID = m1.CONTENTS_ID
        <include refid="evtType_scrap"/>
        ) AS SCRAP_DATE ,
        D1 ,
        (
        CASE
        WHEN D1 = D2 THEN ''
        ELSE D2
        END
        ) AS D2
        FROM
        (
        SELECT
        CONTENTS_ID,
        UPLOAD_TYPE,
        CONTENTS_TITLE,
        CATEGORY_CD,
        VIEW_CNT,
        REG_DATE,
        CORPORATE
        FROM CONTENTS WITH(NOLOCK)
        ) m1
        LEFT OUTER JOIN CATEGORY_TREE m2 ON m1.CATEGORY_CD = m2.CATEGORY_SEQ
        WHERE 1 = 1
        AND   (
        SELECT
        CATEGORY_USE
        FROM CATEGORY WITH(NOLOCK)
        WHERE CATEGORY_SEQ = m1.CATEGORY_CD
        ) = 'Y'
        AND EXISTS (
        SELECT P.CONTENTS_ID
        FROM SCRAP P WITH(NOLOCK)
        WHERE P.CONTENTS_ID = m1.CONTENTS_ID
        <include refid="evtType_scrap"/>
        )
        <if test="userCorporationSeq != 1">
            AND (
            ( TRY_CAST(SUBSTRING(CORPORATE, 1, CHARINDEX(',', CORPORATE + ',') - 1) AS BIGINT) = TRY_CAST(#{userCorporationSeq} AS BIGINT)
            OR TRY_CAST(SUBSTRING(CORPORATE, CHARINDEX(',', CORPORATE + ',') + 1, LEN(CORPORATE)) AS BIGINT) = TRY_CAST(#{userCorporationSeq} AS BIGINT))
            OR ( CORPORATE = 'ALL' )
            )
        </if>
        ORDER BY SCRAP_CNT DESC, SCRAP_DATE, CONTENTS_ID DESC
        <if test="offset != 0">
            OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
        </if>

안녕하세요 이렇게 스크랩한 개수를 카테고리를 WITH절로 구성을 했는데 이게 속도가 많이 저하 된다 하더라구요

그래서 WITH절을 사용하지 않고 조회를 해야 하는데 어디부터 건드려야 할 지 모르겠네요....

 

 

by 마농 [2024.03.17 23:18:24]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
WITH category_tree AS
(
SELECT CONVERT(VARCHAR, a1.category_name) category_depth
     , a1.category_seq
     , a1.category_order
     , a1.category_use
     , a1.up_category_seq
     , a1.category_name
     , a1.category_name d1
     , a1.category_name d2
  FROM category a1 WITH(NOLOCK)
 WHERE a1.up_category_seq = '1'
 UNION ALL
SELECT CONVERT(VARCHAR, CONCAT(ct.category_name, '>', a2.category_name)) category_depth
     , a2.category_seq
     , a2.category_order
     , a2.category_use
     , a2.up_category_seq
     , a2.category_name
     , ct.d1
     , a2.category_name d2
  FROM category a2 WITH(NOLOCK)
 INNER JOIN category_tree ct
    ON ct.category_seq = a2.up_category_seq
)
SELECT m1.upload_type
     , m1.contents_id
     , m1.category_cd
     , ISNULL(m2.category_depth, '-') category_depth
     , m1.contents_title
     , m1.corporate
     , m1.scrap_cnt
     , m1.rnk
     , m1.scrap_date
     , m2.d1
     , NULLIF(m2.d2, m2.d1) d2
  FROM (SELECT m.upload_type
             , m.contents_id
             , m.category_cd
             , m.contents_title
             , m.corporate
             , COUNT(*) scrap_cnt
             , RANK() OVER(ORDER BY COUNT(*) DESC) rnk
             , MAX(p.scrap_date) scrap_date
          FROM contents m WITH(NOLOCK)
         INNER JOIN category c WITH(NOLOCK)
            ON c.category_seq = m1.category_cd
         INNER JOIN scrap p WITH(NOLOCK)
            ON m.contents_id = p.contents_id
         WHERE (CONCAT(',', m.corporate, ',') LIKE CONCAT('%,', #{userCorporationSeq}, ',%') OR m.corporate = 'ALL')
           AND c.category_use = 'Y'
         GROUP BY m1.upload_type
             , m.contents_id
             , m.category_cd
             , m.contents_title
             , m.corporate
        ) m1
  LEFT OUTER JOIN category_tree m2
    ON m1.category_cd = m2.category_seq
 WHERE 1=1
 ORDER BY scrap_cnt DESC, scrap_date, contents_id DESC
 OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입