その他の話題板でリクエストがあったので作ってみました。
登録されたゲーム新着500件です。
紹介1はメーカー、紹介2はGetchu.com、紹介3はerogmapのゲーム紹介ページへのリンクとなっています。
SELECT g.id, '<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||g.id||'">'||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="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||b.id||'">'||b.brandname||'</A><A>'||CASE WHEN b.kind='CIRCLE' THEN '(同人)' ELSE '' END||'</A>' AS ブランド名, '<A>'||CASE WHEN point91='ボーイズラブ' THEN 'BL' WHEN point91='KineticNovel' THEN 'KN' WHEN point91='育成モノ' THEN '育成' ELSE substr(translate(point91,'(',' '),1,3) END||'</A>' AS ジャンル, '<FONT COLOR="#'||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||'">' ||to_char(g.sellday, 'YYYY/MM/DD')|| '</FONT>' AS 発売日, '<FONT COLOR="#'||CASE WHEN g.median >= 85 THEN 'FF0000' WHEN g.median < 85 AND g.median >= 80 THEN '0000FF' ELSE '' END||'">' ||g.median|| '</FONT>' AS 中央値, '<A href="'||g.shoukai||'" target="_brank"><center>'||CASE WHEN g.shoukai like 'http%' THEN '○' ELSE '' END||'</center></A>' AS 紹介1, '<A href="http://www.getchu.com/soft.phtml?id='||g.comike||'" target="_brank"><center>'||CASE WHEN g.comike is not null THEN '○' ELSE '' END||'</center></A>' AS 紹介2, '<A href="http://em.nog.jp/detail.php?game='||g.id||'" target="_brank"><center>'||CASE WHEN g.id is not null THEN '○' ELSE '' END||'</center></A>' AS 紹介3 FROM (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '01%' UNION ALL SELECT id AS id91, null FROM (SELECT id, count(id) FROM gamelist GROUP BY id)tb91 LEFT JOIN (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '01%')tb92 ON id=id91 WHERE point91 IS NULL ORDER BY id91 DESC)tb93, gamelist g, brandlist b WHERE g.brandname=b.id AND g.id=id91 AND g.id>6400 GROUP BY g.id, g.gamename, b.brandname, b.id, g.sellday, g.median, g.shoukai, g.comike, b.kind, g.model, g.erogame, point91 ORDER BY g.id DESC LIMIT 500