데이터베이스 질문과 답변
mysql 순위 매기기
0
2
839
by 밍밍밍밍 [2018.04.16 16:55:16]
안녕하세요 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이라는게 있는지 처음배웠습니다!
) 버튼을 클릭하여 작성 하시면 됩니다.