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

mysql 문자열 숫자 정렬...ROWNUM 정렬문제

by 아쿤 [MySQL] [2017.02.23 18:54:44]

캡처1111.PNG (32,353Bytes)

공지글이 가장위로 그다음 ROWNUM을 DESC로 정렬하려고 하는데

홀수번째 ROWNUM이 빠져서 나옵니다..ㅠㅠ도와주세여

SELECT
CASE
T1.NOTI_YN
WHEN 'Y'
THEN '공지'
ELSE @ROWNUM := @ROWNUM + 1
END AS RNUM,
T1.*
FROM
(SELECT
'N' CHK,
A.ID,
A.CS_BBS_ID,
A.GRP,
A.SORT,
A.LV,
B.P_CATEGORY_ID,
C.CATEGORY_NM P_CS_CATEGORY_NM,
A.CS_CATEGORY_ID,
B.CATEGORY_NM CS_CATEGORY_NM,
A.TITLE,
A.CONTENTS,
A.NAME,
A.VIEWS,
A.NOTI_YN,
A.USE_YN,
A.USER_PWD,
A.UR_USER_ID,
A.USER_NAME,
DATE_FORMAT(A.CREATED, '%Y-%m-%d') AS CREATED,
A.MODIFIED,
(SELECT
COUNT(*)
FROM
CS_BBS_RECOM R
WHERE R.CS_BBS_ID = A.ID) AS CS_BBS_RECOM_CNT,
F.ID CS_BBS_ATTC_ID,
F.PHYS_ATTACHED,
F.REAL_ATTACHED
FROM
CS_BBS A
INNER JOIN CS_CATEGORY B
ON A.CS_CATEGORY_ID = B.ID
INNER JOIN CS_CATEGORY C
ON B.P_CATEGORY_ID = C.ID
LEFT OUTER JOIN CS_BBS_ATTC F
ON A.ID = F.CS_BBS_ID,
(SELECT
@ROWNUM := 0) R
WHERE 1 = 1
AND B.USE_YN != 'N'
AND C.USE_YN != 'N'
AND B.P_CATEGORY_ID = 1
ORDER BY A.GRP ASC,
A.SORT DESC) T1
ORDER BY CAST(RNUM AS INT) DESC

by jkson [2017.02.23 19:19:24]

mysql 문법을 몰라서 원인은 잘 모르겠지만

rnum 컬럼은 '공지' 라는 글자와 숫자형인 rownum의 조합으로 최종 값은 문자형이 될것 같은데

강제로 int형으로 형변환을 시도하네요.

ORDER BY CASE T1.NOTI_YN WHEN 'Y' THEN 99999 ELSE CAST(RNUM AS INT) END DESC

이렇게 해보시면 어떨까요?

by jkson [2017.02.23 21:23:36]

mysql 설치해서 테스트해봤는데요.

--SELECT절에 RNUM
SELECT 
  CASE FG WHEN 'Y' THEN '공지'
		  ELSE @ROWNUM := @ROWNUM + 1
  END AS RNUM
, FG
  FROM
	(
	SELECT A.FG, A.CODE
	  FROM
		(
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'A' CODE UNION ALL
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'B' CODE UNION ALL
		SELECT 'N' FG, 'B' CODE UNION ALL
		SELECT 'N' FG, 'B' CODE
		) A
	) B
	,(SELECT @ROWNUM := 0) C 
 ORDER BY CASE FG WHEN 'Y' THEN 99999 ELSE CAST(RNUM AS decimal) END DESC

--결과

공지	Y
공지	Y
공지	Y
공지	Y
12	N
10	N
8	N
6	N
4	N
2	N


--FROM절에 RNUM
SELECT 
  RNUM
, FG
  FROM
	(
	SELECT A.FG, A.CODE,
	       CASE FG WHEN 'Y' THEN '공지'
			        ELSE @ROWNUM := @ROWNUM + 1
	       END AS RNUM
	  FROM
		(
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'A' CODE UNION ALL
		SELECT 'Y' FG, 'A' CODE UNION ALL
		SELECT 'N' FG, 'B' CODE UNION ALL
		SELECT 'N' FG, 'B' CODE UNION ALL
		SELECT 'N' FG, 'B' CODE
		) A
	) B
	,(SELECT @ROWNUM := 0) C 
 ORDER BY CASE FG WHEN 'Y' THEN 99999 ELSE CAST(RNUM AS decimal) END DESC

--결과

공지	Y
공지	Y
공지	Y
공지	Y
6	N
5	N
4	N
3	N
2	N
1	N

이유는 공부해봐야겠네요.

by jkson [2017.02.23 21:51:38]

CAST 문제였네요.

ORDER BY 절에서 CAST하게 되면 ROWNUM이 또 늘어나네요. 왜 그런지는 아직도 모르겠습니다.

--CAST 하지 않았을 때
SELECT A.FG, A.CODE
     , CASE FG WHEN 'Y' THEN @ROWNUM
		       ELSE @ROWNUM := @ROWNUM + 1
       END AS RNUM
  FROM
	(
	SELECT 'Y' FG, '1' CODE UNION ALL
	SELECT 'Y' FG, '2' CODE UNION ALL
	SELECT 'Y' FG, '3' CODE UNION ALL
	SELECT 'N' FG, '4' CODE UNION ALL
	SELECT 'N' FG, '5' CODE UNION ALL
	SELECT 'N' FG, '6' CODE UNION ALL
	SELECT 'Y' FG, '7' CODE UNION ALL
	SELECT 'N' FG, '8' CODE UNION ALL
	SELECT 'N' FG, '9' CODE UNION ALL
	SELECT 'N' FG, '0' CODE
	) A,(SELECT @ROWNUM := 0) B
ORDER BY  RNUM  DESC

--결과
N	0	6
N	9	5
N	8	4
Y	7	3
N	6	3
N	5	2
N	4	1
Y	1	0
Y	2	0
Y	3	0

--CAST했을 때
SELECT A.FG, A.CODE
     , CASE FG WHEN 'Y' THEN @ROWNUM
		       ELSE @ROWNUM := @ROWNUM + 1
       END AS RNUM
  FROM
	(
	SELECT 'Y' FG, '1' CODE UNION ALL
	SELECT 'Y' FG, '2' CODE UNION ALL
	SELECT 'Y' FG, '3' CODE UNION ALL
	SELECT 'N' FG, '4' CODE UNION ALL
	SELECT 'N' FG, '5' CODE UNION ALL
	SELECT 'N' FG, '6' CODE UNION ALL
	SELECT 'Y' FG, '7' CODE UNION ALL
	SELECT 'N' FG, '8' CODE UNION ALL
	SELECT 'N' FG, '9' CODE UNION ALL
	SELECT 'N' FG, '0' CODE
	) A,(SELECT @ROWNUM := 0) B
ORDER BY CAST(RNUM AS decimal) DESC

--결과
N	0	12
N	9	10
N	8	8
Y	7	6
N	6	6
N	5	4
N	4	2
Y	1	0
Y	2	0
Y	3	0

by 마농 [2017.02.24 14:24:00]

SELECT CASE T1.NOTI_YN WHEN 'Y' THEN '공지' ELSE RNUM END AS RNUM    -- 요기
     , T1.* 
  FROM (SELECT @ROWNUM := @ROWNUM + 1 AS RNUM    -- 요기
             , 'N' CHK
             , A.ID
             , A.CS_BBS_ID
             , A.GRP
             , A.SORT
             , A.LV
             , B.P_CATEGORY_ID
             , C.CATEGORY_NM P_CS_CATEGORY_NM
             , A.CS_CATEGORY_ID
             , B.CATEGORY_NM CS_CATEGORY_NM
             , A.TITLE
             , A.CONTENTS
             , A.NAME
             , A.VIEWS
             , A.NOTI_YN
             , A.USE_YN
             , A.USER_PWD
             , A.UR_USER_ID
             , A.USER_NAME
             , DATE_FORMAT(A.CREATED, '%Y-%m-%d') AS CREATED
             , A.MODIFIED
             , (SELECT COUNT(*) 
                  FROM CS_BBS_RECOM R 
                 WHERE R.CS_BBS_ID = A.ID) AS CS_BBS_RECOM_CNT
             , F.ID CS_BBS_ATTC_ID
             , F.PHYS_ATTACHED
             , F.REAL_ATTACHED 
          FROM CS_BBS A 
         INNER JOIN CS_CATEGORY B 
            ON A.CS_CATEGORY_ID = B.ID 
         INNER JOIN CS_CATEGORY C 
            ON B.P_CATEGORY_ID = C.ID 
          LEFT OUTER JOIN CS_BBS_ATTC F 
            ON A.ID = F.CS_BBS_ID
         CROSS JOIN (SELECT @ROWNUM := 0) R
         WHERE 1 = 1 
           AND B.USE_YN != 'N' 
           AND C.USE_YN != 'N' 
           AND B.P_CATEGORY_ID = 1 
         ORDER BY A.NOTI_YN ASC    -- 요기
                , A.GRP     ASC
                , A.SORT    DESC
        )
 ORDER BY NOTI_YN DESC    -- 요기
        , GRP     DESC    -- 요기
        , SORT    ASC     -- 요기
;

by jkson [2017.02.24 14:46:58]

마농님 제가 회사라서 mysql 테스트할 수가 없어서 그런데 저렇게 하면 분류가 '공지'인 게시물의 rnum은 누락되는 것 아닌가요? 회사에 mysql 깔아도 되는지 모르겠네요. 회사에서 개인적으로 사용할 때 라이센스 문제 없는지..

by 마농 [2017.02.24 15:43:33]

분류가 '공지'인 게시물의 rnum은 누락되는게 아니라 그냥 '공지' 인거죠.

by jkson [2017.02.24 16:00:16]

제가 표현력이 안 좋아서 생각한 바를 잘 전달 못한 것 같습니다.

해당 쿼리가 제 생각에는

공지

이런 형태로 출력될 것 같은데..

작성자님이 최초에 만드신 쿼리를 보면

공지

이런 결과를 의도하신 것 같아서요.

by 마농 [2017.02.24 16:11:44]

네. 제가 작성한 쿼리가. 의도한 결과를 표현하는 쿼리입니다.
서브쿼리에서 공지가 가장 아래로 가게 정렬하여 rownum 구하고
메인쿼리에서 공지가 가장 위로 오게 정렬조건을 서브쿼리에서와 완전 반대로 주고 있습니다.

by jkson [2017.02.24 16:15:59]

그렇군요. 제가 mysql 문법을 몰라서 어제 집에서 첨으로 깔아봤는데 원리를 잘 모르겠어서요.

cast쓸 때 rnum이 배로 뛰는 이유도 잘 모르겠고..

저는 @rownum := @rownum + 1을 select절에서 수행할 때마다 1씩 올라간다 생각했거든요.

위의 쿼리도 어떤 원리로 중간에 누락되지 않는 것인지 궁금하네요.

제가 집에 가서 공부해보겠습니다. 답변 감사합니다^^

by jkson [2017.02.24 16:19:37]

아! 내용 추가하셨네용~ 이해했습니다. order by 단계에서 @rownum := @rownum + 1이 수행되는 것이로군요~~

저는 오라클 처럼 order by가 제일 마지막에 수행되니 select단계에서 @rownum이 올라간다고 생각했습니다.

order by 단계에서 올라가는 것이로군요~^^ 감사합니다. order by에 cast를 씌웠을 때는 내부 함수에서 콜이 그만큼 일어나서

그럴 거라는 추측이 드네요~

by 마농 [2017.02.24 16:29:11]

1. SELECT rnum ... ORDER BY rnum;
2. SELECT rnum ... ORDER BY CAST(rnum);
1의 경우 rnum 을 위한 정렬 공간은 하나 뿐이겠네요.
2의 경우 조회해야할 rnum 외에 정렬해야할 CAST(rnum) 을 위한 공간이 하나 더 할당 되겠네요.
즉, rnum 이 두번 표현되는 것이고,
결과적으로 @rownum := @rownum + 1 이 두번 수행되는 듯.

by jkson [2017.02.24 16:36:39]

넵 이제 이해가 되네요~

어제 테스트하다가 버그인가? 하는 생각을 했는데 이유가 다 있었군요~

남은 근무 시간 후딱 보내시고 불금에 좋은 시간 보내세요. 감사합니다~~

댓글등록

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

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

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

데이터베이스 질문과 답변