상위 30% 평균값 구하는 쿼리 > MySQL

STUDY ROOM

MySQL

상위 30% 평균값 구하는 쿼리

페이지 정보

작성자 JMStudy 작성일13-03-19 16:23 조회4,006회 댓글0건

본문

select
 a.tot as tot_topten, round(a.tot / a.cnt,1) as avg_topten
from (
  select count(*) as cnt, sum(score) as tot from (
    select
     @rnum:=@rnum+1 as rownum, a.*
    from (select @rnum:=0) r, reportcard a where sdate between '2013-03-01' and '2013-03-19'
    order by a.score desc
  ) a
  where a.rownum <= (select round(count(*)*0.3) from reportcard where sdate between '2013-03-01' and '2013-03-19')
  order by a.score desc
) a
댓글목록

등록된 댓글이 없습니다.