人気声優さんランキングの実行結果

SQLの説明

お気に入りクリエイターの投票を集計します。

検索結果

実行したSQL

      select rank() over (order by round(sum((10 - uc1.rank) * 1.0 / uc3.point * (uc3.count / 10 + 1)), 2) desc) as ランク
     , cl.id as ID
     , chr(60)||'a href='||chr(34)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/creater.php?creater='||cl.id||chr(34)||chr(62)||cl.name||chr(60)||'/a'||chr(62) as 声優
     , round(sum((10 - uc1.rank) * 1.0 / uc3.point * (uc3.count / 10 + 1)), 2) as ポイント
     , count(uc1.uid) as 投票数
  from (select uc0.*
          from usercreater as uc0
         where uc0.creater in (select ss.creater
                                 from shokushu as ss, gamelist as gl
                                where ss.game = gl.id
                                  and ss.shubetu = 5
                                  and ss.shubetu_detail = 1
                                  and gl.erogame
                                group by ss.creater
                               having count(ss.game) >= 3)
       ) as uc1
       left join
       (select uc2.uid, count(uc2.creater), sum(10 - uc2.rank) as point
          from usercreater as uc2
         where uc2.creater in (select ss.creater
                                 from shokushu as ss, gamelist as gl
                                where ss.game = gl.id
                                  and ss.shubetu = 5
                                  and ss.shubetu_detail = 1
                                  and gl.erogame
                                group by ss.creater
                               having count(ss.game) >= 3)
         group by uid
        having count(uc2.creater) between 3 and 100
       ) as uc3
       using (uid)
     , createrlist as cl
 where uc1.creater = cl.id
 group by uc1.creater, cl.id, cl.name
having sum(uc1.rank / uc3.point) is not null
 limit 200