SELECT CASE WHEN g.dmm_genre='digital' AND g.dmm_genre_2='pcgame' THEN '<a href="https://dlsoft.dmm.co.jp/detail/' || g.dmm || '/" target="_blank">' || '<img src="http://pics.dmm.co.jp/digital/pcgame/' || g.dmm || '/' || g.dmm || 'ps.jpg"></a>'
WHEN g.dmm_genre='digital' AND g.dmm_genre_2='doujin' THEN '<a href="https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=' || g.dmm || '/" target="_blank">' || '<img src="https://doujin-assets.dmm.co.jp/digital/game/' || g.dmm || '/' || g.dmm || 'pr.jpg" width="150" height=auto></a>'
WHEN g.dlsite_id IS NOT NULL AND (g.dlsite_domain='pro' OR g.dlsite_domain='soft') THEN '<a href="https://www.dlsite.com/' || g.dlsite_domain || '/work/=/product_id/' || g.dlsite_id || '.html/" target="_blank">' || '<img src="https://img.dlsite.jp/modpub/images2/work/professional/' || left(g.dlsite_id,2) || CAST(right(left(g.dlsite_id,5),3) AS INTEGER)+1 || '000/' || g.dlsite_id || '_img_main.jpg" width="150" height=auto></a>'
WHEN g.dlsite_id IS NOT NULL THEN '<a href="https://www.dlsite.com/' || g.dlsite_domain || '/work/=/product_id/' || g.dlsite_id || '.html/" target="_blank">' || '<img src="https://img.dlsite.jp/modpub/images2/work/doujin/' || left(g.dlsite_id,2) || CAST(right(left(g.dlsite_id,5),3) AS INTEGER)+1 || '000/' || g.dlsite_id || '_img_main.jpg" width="150" height=auto></a>'
ELSE '<a href="https://www.dmm.co.jp/mono/pcgame/-/detail/=/cid=' || g.dmm || '/" target="_blank">' || '<img src="https://pics.dmm.co.jp/mono/game/' || g.dmm || '/' || g.dmm || 'ps.jpg"></a>' END AS 画像, '<a href="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||g.id||'">'||g.gamename||'</a>' タイトル, b.brandname ブランド, g.median 中央値, g.count2 データ数
FROM gamelist g
INNER JOIN brandlist b
ON b.id = g.brandname
WHERE g.axis_of_soft_or_hard = '-1'
AND g.median < '71'
ORDER BY g.median DESC, g.count2 DESC