【おかず順】おかずに使えるゲームの実行結果
SQLの説明
条件：「とても使えた」×2+「かなり使えた」+「だいぶ使えた÷2」+「それなりに使えた÷4」-「あまり使えなかった」-「まったく使えなかった×2」＞3である
表示順：条件結果が高い順とする
発売年別順に並び替えたものはこちら→ https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/usersql_exec.php?sql_id=2607
キャンペーンセールで抽出したものはこちら→ https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/usersql_exec.php?sql_id=2729
検索結果
実行したSQL
SELECT '<a href="https://dlsoft.dmm.co.jp/detail/' || gamelist.dmm || '/" target="_blank">' || '<img src="http://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'ps.jpg"></a>' AS 画像
,'<table width="250" class="none_table"><tr><td class="none_td"><a href="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game=' || gamelist.id || '#ad" target="_blank">' || gamelist.gamename || '</a></td></tr></table>' || CASE WHEN TB_CAMP.gid ISNULL THEN '' ELSE TB_CAMP.キャンペーン名 END AS タイトル
,CASE WHEN gamelist.dmm_sample_image_count IN (1,2) and gamelist.dmm_genre='digital' and gamelist.dmm_genre_2='pcgame'
THEN '<table><tr><td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-001.jpg" width="150" height=auto></td>
<td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-002.jpg" width="150" height=auto></td></tr></table>'
WHEN gamelist.dmm_sample_image_count = 3 and gamelist.dmm_genre='digital' and gamelist.dmm_genre_2='pcgame'
THEN '<table><tr><td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-001.jpg" width="150" height=auto></td>
<td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-002.jpg" width="150" height=auto></td></tr>
<tr><td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-003.jpg" width="150" height=auto></td></tr></table>'
WHEN gamelist.dmm_sample_image_count >= 4 and gamelist.dmm_genre='digital' and gamelist.dmm_genre_2='pcgame'
THEN '<table><tr><td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-001.jpg" width="150" height=auto></td>
<td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-002.jpg" width="150" height=auto></td></tr>
<tr><td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-003.jpg" width="150" height=auto></td>
<td><img src="https://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'jp-004.jpg" width="150" height=auto></td></tr></table>'
END AS ｻﾝﾌﾟﾙ
,gamelist.sellday as 発売日
,CASE WHEN brandlist.url IS NOT NULL THEN '<a href="' || brandlist.url || '" target="_blank">' || brandlist.brandname || '</a>' WHEN brandlist.lost='t' THEN brandlist.brandname || ' (解散)' ELSE brandlist.brandname END AS ブランド
,gamelist.median as 中央値
,gamelist.count2 as データ数
,(CASE WHEN gamelist.dmm_subsc IS NOT NULL THEN '有' END) AS DMMﾌﾟﾚﾐｱﾑ
,sum(case when userreview.okazu_tokuten=4 then 1 else 0 end) as とても使えた
,sum(case when userreview.okazu_tokuten=3 then 1 else 0 end) as かなり使えた
,sum(case when userreview.okazu_tokuten=2 then 1 else 0 end) as だいぶ使えた
,sum(case when userreview.okazu_tokuten=1 then 1 else 0 end) as それなりに使えた
,sum(case when userreview.okazu_tokuten=-1 then 1 else 0 end) as あまり使えなかった
,sum(case when userreview.okazu_tokuten=-2 then 1 else 0 end) as まったく使えなかった
FROM ((gamelist INNER JOIN brandlist ON gamelist.brandname = brandlist.id)
INNER JOIN userreview ON gamelist.ID=userreview.game)
LEFT OUTER JOIN (SELECT gamelist.id as gid,'<table width="250"><tr><td>' || STRING_AGG('【' || storelist.name || '】<a href="' || campaignlist.url || '" target="_blank">' || campaignlist.name || '</a>(' || campaign_game.content || ')', '</td><tr><tr><td>') || '</td></tr></table>' as キャンペーン名
FROM ((campaign_game INNER JOIN campaignlist ON campaign_game.campaign = campaignlist.id)
INNER JOIN gamelist ON campaign_game.game = gamelist.id)
INNER JOIN storelist ON campaignlist.store = storelist.id
WHERE campaignlist.end_timestamp >= CURRENT_DATE
AND gamelist.count2 IS NOT NULL
GROUP BY gamelist.count2,gamelist.id
ORDER BY gamelist.median DESC,gamelist.count2 DESC) as TB_CAMP ON gamelist.ID=TB_CAMP.gid
WHERE gamelist.count2 IS NOT NULL
AND userreview.okazu_tokuten IN (4, 3, 2, 1, -1, -2)
GROUP BY gamelist.sellday,gamelist.count2,gamelist.median,gamelist.dmm,gamelist.id,brandlist.url,brandlist.brandname,brandlist.lost,TB_CAMP.gid,TB_CAMP.キャンペーン名
having sum(case when userreview.okazu_tokuten=4 then 1 else 0 end)*2+sum(case when userreview.okazu_tokuten=3 then 1 else 0 end)+sum(case when userreview.okazu_tokuten=2 then 1 else 0 end)/2+sum(case when userreview.okazu_tokuten=1 then 1 else 0 end)/4-sum(case when userreview.okazu_tokuten=-1 then 1 else 0 end)-sum(case when userreview.okazu_tokuten=-2 then 1 else 0 end)*2>3
ORDER BY sum(case when userreview.okazu_tokuten=4 then 1 else 0 end)*2+sum(case when userreview.okazu_tokuten=3 then 1 else 0 end)+sum(case when userreview.okazu_tokuten=2 then 1 else 0 end)/2+sum(case when userreview.okazu_tokuten=1 then 1 else 0 end)/4-sum(case when userreview.okazu_tokuten=-1 then 1 else 0 end)-sum(case when userreview.okazu_tokuten=-2 then 1 else 0 end)*2 DESC,gamelist.count2 DESC,gamelist.median DESC