十時間以内かつ７５点以上の作品。の実行結果

SQLの説明

検索結果

実行したSQL

            SELECT '<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||subquery.id||'">'||subquery.gamename||'</A>' AS ゲーム名
     , '<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||brandlist.id||'">'||brandlist.brandname||'</A>' AS ブランド名
     , subquery.sellday AS 発売日
     , subquery.calc_avrg_tokuten AS "得点"
     --, subquery.median AS 得点（中央値）
     --, subquery.stdev AS 標準偏差
     , subquery.count2 AS データ数
     , subquery.avrgplaytime AS 平均プレイ時間
     , subquery.genre AS ジャンル
  FROM 
 (SELECT 
  eg.id as id
  ,eg.gamename as gamename
  ,eg.genre as genre
  ,eg.sellday as sellday
  ,eg.median median
  ,eg.stdev as stdev
  ,eg.count2 as count2
  ,eg.brandname as brandid
  ,round(avg(tokuten),0) as calc_avrg_tokuten
  ,round(avg(total_play_time),1) as avrgplaytime
 FROM userreview as e
 INNER JOIN gamelist eg
         ON e.game = eg.id
 WHERE sellday > '2000-01-01 00:00:00'
  AND count2 >= 50
 GROUP BY eg.gamename
        , eg.sellday
        , eg.id
 ORDER BY avg(tokuten) DESC) as subquery
 INNER JOIN brandlist
  ON subquery.brandid = brandlist.id
 WHERE subquery.avrgplaytime <10
  AND subquery.calc_avrg_tokuten >= 75
 ORDER BY subquery.avrgplaytime ASC