SET @rank=0; SELECT @rank:=@rank+1 AS rank, TA.KEYWORD_HISTORY_KEYWORD, TA.cnt FROM (SELECT KEYWORD_HISTORY_KEYWORD, count(KEYWORD_HISTORY_KEYWORD) as cnt FROM tbl_keyword_history WHERE REG_DATE BETWEEN DATE_FORMAT('2016-01-01', '%Y-%m-%d') AND DATE_FORMAT('2016-12-31', '%Y-%m-%d') GROUP BY KEYWORD_HISTORY_KEYWORD order by cnt ) TA
이렇게하면 나오긴하는데 ...
랭크랑 cnt랑 따로 놀고 있고 랭크가
1 1
2 1
3 3
이렇게 나오게 하고 싶은데 ㅠㅠ
어떻게하면 될까요 ㅠ
1. 정렬에 DESC 가 들어가야 하는것 아닌가요?
- ORDER BY cnt DESC
2. date_format 는 날짜를 문자로 바꾸는 함수입니다.
- 굳이 사용할 이유가 보이질 않네요?
3. 질문할 때 DBMS 를 명시해 주세요.
- mySQL
SELECT @rnum := @rnum + 1 AS rnum , @rank := CASE @cnt WHEN cnt THEN @rank ELSE @rnum END AS rank , ta.keyword_history_keyword , @cnt := ta.cnt AS cnt FROM (SELECT keyword_history_keyword , COUNT(keyword_history_keyword) AS cnt FROM tbl_keyword_history WHERE reg_date BETWEEN '2016-01-01' AND '2016-12-31' GROUP BY keyword_history_keyword ORDER BY cnt DESC ) ta , (SELECT @rank := 0 , @rnum := 0 , @cnt := 0 ) tb ;