発売日とそれ以前の得点数の合計
SELECT '<A href='||chr(34)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||g.id||''||chr(34)||'>'||g.gamename||'</A><A>'||CASE WHEN g.model='PC' AND g.erogame='f' THEN '(非18禁)' WHEN g.model='PC' THEN '' ELSE '('||g.model||')' END||'</A>' AS ゲーム名, '<A href='||chr(34)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||b.id||''||chr(34)||'>'||b.brandname||'</A><A>'||CASE WHEN b.kind='CIRCLE' THEN '(同人)' ELSE '' END||'</A>' AS ブランド名, '<FONT COLOR='||chr(34)||'#'||CASE WHEN current_date-g.sellday+1<=180 AND current_date-g.sellday+1>=31 THEN '0000FF' WHEN current_date-g.sellday+1<31 AND current_date-g.sellday+1>=1 THEN 'FF0000' WHEN current_date -g.sellday+1<1 THEN '999999' ELSE '' END||''||chr(34)||'>' ||to_char(g.sellday,'yyyy/mm/dd')|| '</FONT>' AS 発売日, '<FONT COLOR='||chr(34)||'#'||CASE WHEN point1 >= 85 THEN 'FF0000' WHEN point1 < 85 AND point1 >= 80 THEN '0000FF' WHEN point1 < 70 THEN '999999' ELSE '' END||''||chr(34)||'>' ||to_char(point1,'990.99')|| '</FONT>' AS 平均点, '<FONT COLOR='||chr(34)||'#'||CASE WHEN point2 >= 500 THEN 'FF0000' WHEN point2 < 500 AND point2 >=300 THEN '0000FF' ELSE '' END||''||chr(34)||'>' ||point2|| '</FONT>' AS データ数 FROM (SELECT game, avg(tokuten) AS point1, count(tokuten) AS point2 FROM (SELECT u.game,g.gamename,g.sellday,u.tokuten,u.tourokubi,u.play_tourokubi FROM userreview AS u,gamelist AS g WHERE u.game=g.id AND date(u.play_tourokubi) - date(g.sellday)<=0)tb2 GROUP BY game ) AS j , gamelist g, brandlist b WHERE g.brandname=b.id and game=g.id and point2>0 ORDER BY point2 DESC,sellday DESC,point1 desc