人気原画さんランキングの実行結果

SQLの説明

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

検索結果

実行したSQL

      select rank() over (order by round(sum((10 - uc1.rank) * 1.0 / uc3.point), 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), 2) as ポイント
     , count(uc1.uid) as 投票数
  from (select uc0.*
          from usercreater as uc0
         where uc0.creater in (select creater
                                 from shokushu
                                where shubetu = 1
                                  and shubetu_detail = 1
                                group by creater
                               having count(game) >= 3)
       ) as uc1
       left join
       (select uc2.uid, sum(10 - uc2.rank) as point
          from usercreater as uc2
         where uc2.creater in (select creater
                                 from shokushu
                                where shubetu = 1
                                  and shubetu_detail = 1
                                group by creater
                               having count(game) >= 3)
         group by uid
        having count(uc2.creater) >= 2 
       ) 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