[MySql]5.x 버전에서 순위(rank ) 구하기
MySql 8.x 버전에서는 윈도우 함수(Window function)를 지원한다. 이 경우 rank(), dense_rank() 함수를 이용하면 쉽게 구할 수 있다.
반면 그 이전 버전 5.x 에서는 아래와 같이 구할 수 있다.
-- rank select id , name , age , gender , case when @part = gender then @rn := @rn + 1 else @rn := 1 end as rn , @part := gender as part from (select 1 id, '홍길동' as name, 10 age, 'M' gender union all select 2 id, '성춘향' as name, 15 age, 'F' gender union all select 3 id, '이몽룡' as name, 30 age, 'M' gender union all select 4 id, '변사또' as name, 7 age, 'M' gender union all select 5 id, '콩쥐' as name, 43 age, 'F' gender union all select 6 id, '방자' as name, 32 age, 'M' gender union all select 7 id, '향단이' as name, 26 age, 'F' gender) as tmp , (select @rn := 0, @part := null) as x order by gender asc, age asc ; /* id name age gender rn part 2 성춘향 15 F 1 F 7 향단이 26 F 2 F 5 콩쥐 43 F 3 F 4 변사또 7 M 1 M 1 홍길동 10 M 2 M 3 이몽룡 30 M 3 M 6 방자 32 M 4 M */ -- dense_rank select id , name , age , gender , case when @part = gender and age = @pre_age then @rn when @part = gender then @rn := @rn + 1 else @rn := 1 end as rk , @part := gender as part , @pre_age := age as pre_age from (select 1 id, '홍길동' as name, 10 age, 'M' gender union all select 2 id, '성춘향' as name, 15 age, 'F' gender union all select 3 id, '이몽룡' as name, 30 age, 'M' gender union all select 4 id, '변사또' as name, 7 age, 'M' gender union all select 5 id, '콩쥐' as name, 26 age, 'F' gender union all select 6 id, '방자' as name, 32 age, 'M' gender union all select 7 id, '향단이' as name, 26 age, 'F' gender union all select 8 id, '심청이' as name, 32 age, 'F' gender) as tmp , (select @rn := 0, @part := null, @pre_age := null) as x order by gender asc, age asc ; /* id name age gender rk part pre_age 2 성춘향 15 F 1 F 15 5 콩쥐 26 F 2 F 26 7 향단이 26 F 2 F 26 8 심청이 32 F 3 F 32 4 변사또 7 M 1 M 7 1 홍길동 10 M 2 M 10 3 이몽룡 30 M 3 M 30 6 방자 32 M 4 M 32 */
최신 댓글