mysql 순위 매기기 0 2 194

by 밍밍밍밍 [2018.04.16 16:55:16]


20180416_165239.png (5,284Bytes)

안녕하세요 mysql 순위를 매기고 싶습니다.

아래 쿼리를 실행결과를 첨부파일에 첨부했습니다.

예를 들어 loginCnt가 둘다 8일 경우 rank 컬럼에 둘다 2로 표시하고싶습니다.

어떻게 수정해야되는지 답변좀 부탁드리겠습니다.

 SELECT userId, userNm, loginCnt, rank 
 FROM (
 SELECT
      A.USERID AS userId,
	    (SELECT USERNAME FROM se_users B WHERE A.USERID = B.USERID) AS userNm,
			COUNT(userId) AS loginCnt,
      @vRank := @vRank + 1 AS rank
	    FROM se_users_log A, (SELECT @vRank := 0) AS r
	    WHERE 1=1
      GROUP BY A.USERID
		 	ORDER BY loginCnt DESC ) AS CNT

 

by 마농 [2018.04.16 19:33:29]
-- 1. 변수를 이용하는 방법 --
SELECT userId, userNm, loginCnt
     , @vRnum := @vRnum + 1 AS Rnum
     , @vRank := CASE WHEN @vCnt = loginCnt THEN @vRank ELSE @vRnum END AS Rank
     , @vCnt  := loginCnt
  FROM (SELECT a.userid   AS userId
             , b.username AS userNm
             , COUNT(*)   AS loginCnt
          FROM se_users_log a
          LEFT OUTER JOIN se_users b
            ON a.userid = b.userid
         GROUP BY a.userid, b.username
         ORDER BY loginCnt DESC
        ) a
     , (SELECT @vRank := 0, @vRnum := 0, @vCnt := 0) r
;
-- 2. Self Join --
SELECT a.userId, a.userNm, a.loginCnt
     , COUNT(b.userId) + 1 AS Rank
  FROM (SELECT a.userid   AS userId
             , b.username AS userNm
             , COUNT(*)   AS loginCnt
          FROM se_users_log a
          LEFT OUTER JOIN se_users b
            ON a.userid = b.userid
         GROUP BY a.userid
        ) a
  LEFT OUTER JOIN
       (SELECT a.userid   AS userId
             , COUNT(*)   AS loginCnt
          FROM se_users_log a
         GROUP BY a.userid
        ) b
    ON a.loginCnt < b.loginCnt
 GROUP BY a.userId, a.userNm, a.loginCnt
 ORDER BY loginCnt DESC
;

 


by 밍밍밍밍 [2018.04.17 09:45:45]

답변감사드립니다 해결했습니다.! self join이라는게 있는지 처음배웠습니다! 

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