実行したSQL
SELECT 'DUMMY'
/*
<SQL1> zz.creater
,zz.name zz_name
,cl.furigana || '<br>'
|| CASE WHEN uc.uid IS NOT NULL THEN '<img src="/~ap2/ero/toukei_kaiseki/heart16-001.gif">' ELSE '' END || '<a href="/~ap2/ero/toukei_kaiseki/creater.php?creater=' || zz.creater || '" target="_blank">' || zz.name || '</a><br>'
|| '声優総出演数 : ' || zz.cnt_seiyu || '<br>'
|| '歌手総出演数 : ' || zz.cnt_kashu || '<br>'
|| CASE WHEN cl.circle IS NULL THEN '' ELSE 'circle : ' || cl.circle || '<br>' END
|| CASE WHEN cl.url IS NULL THEN '' ELSE 'OHP : <a href="' || cl.url || '" target="_blank">' || COALESCE(cl.title,'Offical HP') ||'</a><br>' END
|| CASE WHEN cl.twitter_username IS NULL THEN '' ELSE 'twitter : <a href="https://twitter.com/' || cl.twitter_username || '" target="_blank">' || cl.twitter_username ||'</a><br>' END
|| CASE WHEN cl.blog IS NULL THEN '' ELSE 'BLOG : <a href="' || cl.blog || '" target="_blank">' || COALESCE(cl.blog_title,'ブログ') ||'</a><br>' END
|| CASE WHEN cl.pixiv IS NULL THEN '' ELSE 'pixiv : <a href="https://www.pixiv.net/users/' || cl.pixiv || '" target="_blank">' || cl.pixiv ||'</a><br>' END
|| CASE WHEN cl.cien IS NULL THEN '' ELSE 'cien : <a href="https://ci-en.net/creator/' || cl.cien || '" target="_blank">' || cl.cien ||'</a><br>' END
|| CASE WHEN cl.fantia IS NULL THEN '' ELSE 'fantia : <a href="https://fantia.jp/fanclubs/' || cl.fantia || '" target="_blank">' || cl.fantia ||'</a><br>' END
名前
,zz.cnt_seiyu 総出演数
,zz.cnt_sort ソート
,zz.cnt_seiyu
,zz.cnt_kashu
,'<div id="creater-' || zz.creater || '"></div>' graph
,zz."声優ALL19xx" || '/' || zz."声優メイン19xx" || '/' || zz."歌手19xx" "19xx"
{SQL_SELECT}
,zz."声優ALL未定" || '/' || zz."声優メイン未定" || '/' || zz."歌手未定" "未定"
FROM (
SELECT
sh.creater
,cl.name
,{SQL_SORT_COLUMN} cnt_sort
,SUM(CASE WHEN sh.shubetu = 5 THEN 1 ELSE 0 END) cnt_seiyu
,SUM(CASE WHEN sh.shubetu = 6 THEN 1 ELSE 0 END) cnt_kashu
,SUM(CASE WHEN date_part('year', gl.sellday) < 2000 AND sh.shubetu = 5 THEN 1 ELSE 0 END) "声優ALL19xx"
{SQL_声優ALL}
,SUM(CASE WHEN date_part('year', gl.sellday) = 2030 AND sh.shubetu = 5 THEN 1 ELSE 0 END) "声優ALL未定"
,SUM(CASE WHEN date_part('year', gl.sellday) < 2000 AND sh.shubetu = 5 AND sh.shubetu_detail = 1 THEN 1 ELSE 0 END) "声優メイン19xx"
{SQL_声優メイン}
,SUM(CASE WHEN date_part('year', gl.sellday) = 2030 AND sh.shubetu = 5 AND sh.shubetu_detail = 1 THEN 1 ELSE 0 END) "声優メイン未定"
,SUM(CASE WHEN date_part('year', gl.sellday) < 2000 AND sh.shubetu = 6 THEN 1 ELSE 0 END) "歌手19xx"
{SQL_歌手}
,SUM(CASE WHEN date_part('year', gl.sellday) = 2030 AND sh.shubetu = 6 THEN 1 ELSE 0 END) "歌手未定"
FROM
shokushu sh
INNER JOIN gamelist gl ON sh.game = gl.id
INNER JOIN createrlist cl ON sh.creater = cl.id AND (sh.shubetu = 5 OR sh.shubetu = 6)
WHERE
cl.name ILIKE '%{SQL_CREATOR_NAME}%'
GROUP BY
sh.creater
,cl.name
) zz
INNER JOIN createrlist cl ON zz.creater = cl.id
LEFT OUTER JOIN usercreater uc ON zz.creater = uc.creater AND uc.uid = '{SQL_USER_ID}'
ORDER BY
CASE WHEN uc.uid IS NOT NULL THEN {SQL_SORT_FAVORITE} ELSE 0 END DESC
,cnt_sort DESC
,{SQL_SORT_COLUMN2} DESC
LIMIT {SQL_LIMIT}
</SQL1>
<SQL2>
-- fo.rank
-- ,fo.brandname_id
-- ,fo.game_id
-- ,fo.sellday
fo.画像
,fo.brand_link || ' ' || fo.brandname_a
|| '<br>' || fo.game_link || ' ' || fo.セール中 || fo.play_status || fo.gamename_a
|| '<br>' || fo.sellday
|| '<br>中央値/データ数 : ' || CASE WHEN fo.count2 > 0 THEN fo.median || '/' || fo.count2 ELSE 'NODATA' END
ゲームタイトル
,STRING_AGG(COALESCE(fo.img, '')
|| '<div class="' || CASE fo.shubetu_detail WHEN '1' THEN 'seiyu_main ' WHEN '2' THEN 'seiyu_sub ' WHEN '3' THEN 'seiyu_other ' ELSE '' END || '">' || fo.shubetu_detail_name || CASE WHEN fo.char_furigana IS NULL THEN '' ELSE '(' || fo.char_furigana || ')' END
|| CASE WHEN fo.一致 = '一致' OR fo.一致 = '無し' THEN '' ELSE '<br>' || fo.アイコンゲーム名 || '(候補表示)' END
-- || '<hr>【' || fo.prev_char || '】<br>【' || fo.curr_char || '】'
-- || '<hr>【' || fo.prev_char2 || '】<br>【' || fo.curr_char2 || '】'
|| '</div>'
,'<hr class="char_br">'
ORDER BY
CASE WHEN fo.io_game = fo.gl_id THEN 1 ELSE 0 END DESC
,CASE WHEN fo.char_furigana IS NOT NULL THEN fo.アイコンゲーム発売日 ELSE '1900-01-01' END DESC, fo.char_furigana
) キャラクター
FROM (
SELECT
RANK() OVER (PARTITION BY gl.id, COALESCE(io.game, 0), io.name ORDER BY CASE WHEN io.game = gl.id THEN 1 ELSE 0 END DESC) rank
,CASE
WHEN bl.kind = 'CIRCLE' THEN '<img src="' || foo.doujin_image || '" width="125px">'
ELSE '<img src="' || foo.mediumimage || '" width="125px">'
END 画像
,gl.brandname brandname_id
,sh.game game_id
,'<a href="./usersql_exec.php?sql_id={sql_id}&pov_top5=true&direct=true&scenario_kind=scenario_all&illust_kind=illust_all&music_kind=music_all&brand_id=' || gl.brandname || '&user_id={user_id}" target="_blank">★</a>' brand_link
,'<a href="./usersql_exec.php?sql_id={sql_id}&pov_top5=true&direct=true&scenario_kind=scenario_all&illust_kind=illust_all&music_kind=music_all&game_id=' || gl.id || '&user_id={user_id}" target="_blank">★</a> ' game_link
,'<a href="game.php?game=' || gl.id || '" target="_blank">' || gl.gamename || '</a>'
|| CASE WHEN gl.model = 'PC' THEN CASE WHEN gl.erogame = true THEN '' ELSE '(非18禁)' END ELSE '(' || gl.model || CASE WHEN gl.erogame = true THEN '' ELSE '/非18禁' END || ')' END gamename_a
,'<a href="brand.php?brand=' || bl.id || '" target="_blank">' || bl.brandname || '</a>' || CASE WHEN bl.kind = 'CIRCLE' THEN '<span class="brand_circle">(同人)</span>' ELSE '<span class="brand_corp"></span>' END brandname_a
,gl.sellday
,sh.shubetu_detail
,sh.shubetu_detail_name character_name
,'<img src="{image_url}' || io.image_dir || '" class="chara_img">' img
,COALESCE('<a href="./character.php?character=' || io.a_character || '" target="_blank">' || sh.shubetu_detail_name || '</a>', sh.shubetu_detail_name) shubetu_detail_name
,io.char_furigana
,COALESCE('<a href="game.php?game=' || io.game || '" target="_blank">' || io.gamename || '</a>', gl.gamename) アイコンゲーム名
,CASE WHEN io.image_dir IS NOT NULL AND gl.id = io.game THEN '一致' WHEN io.image_dir IS NOT NULL AND gl.id != io.game THEN '不一致' ELSE '無し' END 一致
,lag(gl.id || '/' || COALESCE(io.game, 0) || '/' || sh.shubetu_detail_name, 1, '') OVER (ORDER BY sh.shubetu_detail_name, COALESCE(io.sellday, '1900-01-01') DESC, gl.id) prev_char
,lag(gl.id || '/' || sh.shubetu_detail_name, 1, '') OVER (ORDER BY sh.shubetu_detail_name, COALESCE(io.sellday, '1900-01-01') DESC, gl.id) prev_char2
,gl.id || '/' || COALESCE(io.game, 0) || '/' || sh.shubetu_detail_name curr_char
,gl.id || '/' || sh.shubetu_detail_name curr_char2
,gl.furigana
,io.sellday アイコンゲーム発売日
,io.game io_game
,gl.id gl_id
,COALESCE(gl.median, 0) median
,COALESCE(gl.count2, 0) count2
,CASE
WHEN uv.play= 't' THEN '<img class="user_game_status" src="/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/sub_black_accept.png">'
WHEN uv.possession = 't' THEN '<img class="user_game_status" src="/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/archive.png">'
WHEN uv.before_purchase_will != '' THEN '<img class="user_game_status" src="/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/shopping_cart.png">'
ELSE ''
END play_status
,COALESCE(セール中, '') セール中
FROM
(SELECT id, game, creater, shubetu, checked, note, shubetu_detail, unnest(string_to_array(CASE WHEN shubetu_detail = 3 THEN COALESCE(shubetu_detail_name, 'その他') ELSE shubetu_detail_name END,'、')) shubetu_detail_name FROM (SELECT * FROM shokushu WHERE shubetu = 5) a) sh
INNER JOIN gamelist gl ON sh.game = gl.id
INNER JOIN brandlist bl ON gl.brandname = bl.id
LEFT OUTER JOIN (SELECT gl.id
, COALESCE(
{amazon_img_line} (/*Amazon*/SELECT replace(al.mediumimage, '_SL160_', '_SL200_') FROM amazon_game AS ag, amazonlist AS al WHERE ag.asin = al.asin AND ag.game = gl.id AND al.mediumimage IS NOT NULL ORDER BY al.asin LIMIT 1),
(/*DMM*/'https://pics.dmm.co.jp/'||gl.dmm_genre||'/pcgame/'||gl.dmm||'/'||gl.dmm||'p'||CASE WHEN gl.dmm_genre_2 = 'doujin' THEN 't' ELSE 's' END||'.jpg')
, (/*DLsite*/'https://img.dlsite.jp/modpub/images2/work/'||CASE WHEN gl.dlsite_domain = 'pro' THEN 'professional' WHEN gl.dlsite_domain = 'pro2' OR left(gl.dlsite_id, 2) = 'VJ' THEN 'professional' ELSE 'doujin' END||'/'||left(gl.dlsite_id, 2)||right('000000'||(ceil(right(gl.dlsite_id, 6)::real / 1000) * 1000)::text, 6)||'/'||gl.dlsite_id||'_img_sam.jpg')
, (/*DiGiket*/'https://img.digiket.net/cg/' || to_number(substring(gl.digiket, 4,3),'999') || '/'||gl.digiket||'_2.jpg')
, (/*Gyutto*/'https://gyutto.com/data/item_img/'||left(gl.gyutto_id, -2)||'/'||gl.gyutto_id||'/'||gl.gyutto_id||'_p_m2.jpg')
, (/*Getchu*/'https://image.getchu.com/afimage/'||floor(gl.comike::real / 1000)::text||'000/'||gl.comike||'/afimg_'||gl.comike||'_120.jpg')
, '') AS mediumimage
, COALESCE(
(/*DLsite*/'https://img.dlsite.jp/modpub/images2/work/'||CASE WHEN gl.dlsite_domain = 'pro' THEN 'professional' WHEN gl.dlsite_domain = 'pro2' OR left(gl.dlsite_id, 2) = 'VJ' THEN 'professional' ELSE 'doujin' END||'/'||left(gl.dlsite_id, 2)||right('000000'||(ceil(right(gl.dlsite_id, 6)::real / 1000) * 1000)::text, 6)||'/'||gl.dlsite_id||'_img_sam.jpg')
, (/*DMM*/'https://pics.dmm.co.jp/'||gl.dmm_genre||'/pcgame/'||gl.dmm||'/'||gl.dmm||'p'||CASE WHEN gl.dmm_genre_2 = 'doujin' THEN 't' ELSE 's' END||'.jpg')
, (/*DiGiket*/'https://img.digiket.net/cg/' || to_number(substring(gl.digiket, 4,3),'999') || '/'||gl.digiket||'_2.jpg')
, (/*Gyutto*/'https://gyutto.com/data/item_img/'||left(gl.gyutto_id, -2)||'/'||gl.gyutto_id||'/'||gl.gyutto_id||'_p_m2.jpg')
, (/*Getchu*/'https://image.getchu.com/afimage/'||floor(gl.comike::real / 1000)::text||'000/'||gl.comike||'/afimg_'||gl.comike||'_120.jpg')
, '') AS doujin_image
FROM gamelist AS gl
) AS foo ON foo.id = gl.id
LEFT OUTER JOIN userreview uv ON uv.game= gl.id AND uv.uid = '{user_id}'
----------------- 最終セール時価格
LEFT OUTER JOIN (
SELECT sale.game, sale.e
,CASE WHEN sale.e < CURRENT_TIMESTAMP THEN '<span class="not_sale_now">最終セール時: ' || sale.c || ' ' || TO_CHAR(sale.e, 'YYYY-MM-DD') || '</span>' ELSE '<span class="now_on_sale"><img src="./sale.png" class="sale_icon" title="' || sale.name || '" alt="' || sale.name || '"> ' || sale.c || '<span class="text-nowrap">～' || TO_CHAR(sale.e, 'MM-DD HH24:MI') || '</span></span>' END セール時最終
,CASE WHEN sale.e < CURRENT_TIMESTAMP THEN '' ELSE '<img src="./sale.png" class="sale_icon" title="' || sale.name || '" alt="' || sale.name || '">' END セール中
,sale.rownum
FROM (
SELECT sl.name, game,end_timestamp as e, cg.content as c, ROW_NUMBER() OVER (PARTITION BY game ORDER BY end_timestamp DESC, cl.id DESC) rownum
FROM campaignlist cl INNER JOIN campaign_game cg ON cg.campaign=cl.id INNER JOIN storelist sl ON cl.store = sl.id ) sale
WHERE sale.rownum=1) ls ON ls.game = gl.id
-----------------
LEFT OUTER JOIN (
SELECT
bl.id brand_id
,gl.id game
,gl.gamename gamename
,gl.sellday sellday
,COALESCE(io.character, ig.character, ie.character) a_character
,COALESCE(io.stage, ig.stage, ie.stage) stage
,COALESCE(io.image_dir, ig.image_dir, ie.image_dir) image_dir
,ch.name
,ch.furigana char_furigana
FROM
appearance_image_official io
FULL OUTER JOIN appearance_image_gyutto ig ON io.game = ig.game AND io.character = ig.character
FULL OUTER JOIN appearance_image_getchu ie ON io.game = ie.game AND io.character = ie.character
INNER JOIN characterlist ch ON ch.id = COALESCE(io.character, ig.character, ie.character)
INNER JOIN gamelist gl ON gl.id = COALESCE(io.game, ig.game, ie.game)
INNER JOIN brandlist bl ON bl.id = gl.brandname
) io ON (io.name = sh.shubetu_detail_name AND io.game =sh.game) -- OR (io.name = sh.shubetu_detail_name AND io.brand_id = gl.brandname AND io.game != sh.game)
-- 該当ゲーム内にキャラ画像がなければ同一ブランド内から同一キャラ名でキャラ画像を再検索するゲームとキャラ名が一致している場合は優先的に利用する（RANK関数で処理）（とりあえず実装中断）
WHERE
sh.shubetu = 5
{shubetu_detail_line} AND sh.shubetu_detail = 1
{year_line} AND gl.sellday BETWEEN '{year_from}-01-01' AND '{year_to}-12-31'
AND sh.creater = {creator_id}
) fo
WHERE
fo.rank = 1
-- AND fo.prev_char != fo.curr_char
GROUP BY
fo.rank
,fo.画像
,fo.brand_link
,fo.brandname_id
,fo.brandname_a
,fo.game_link
,fo.game_id
,fo.gamename_a
,fo.sellday
,fo.median
,fo.count2
,fo.play_status
,fo.セール中
ORDER BY
fo.sellday DESC
,fo.game_id
</SQL2>
<SQL3>
gl.gamename
,md.musicname 曲名
FROM
(SELECT
gl.id gl_id
,STRING_AGG(gm.category || '(' || ml.name || ')', '<br>' ORDER BY gm.category) musicname
FROM
game_music gm LEFT OUTER JOIN musiclist ml ON gm.music = ml.id
LEFT OUTER JOIN gamelist gl ON gm.game = gl.id
LEFT OUTER JOIN singer si ON ml.id = si.music
LEFT OUTER JOIN createrlist cl ON si.creater = cl.id
WHERE
cl.id = {SQL_CREATOR_ID}
{SQL_YEAR_LINE} AND gl.sellday BETWEEN '{SQL_YEAR_FROM}-01-01' AND '{SQL_YEAR_TO}-12-31'
GROUP BY
gl.id
) md LEFT OUTER JOIN gamelist gl ON md.gl_id = gl.id
</SQL3>
<SQL4>
CASE
WHEN bl.kind = 'CIRCLE' THEN '<img src="' || foo.doujin_image || '" width="125px">'
ELSE '<img src="' || foo.mediumimage || '" width="125px">'
END 画像
/*
,fo.brand_link || ' ' || fo.brandname_a
|| '<br>' || fo.game_link || ' ' || fo.セール中 || fo.play_status || fo.gamename_a
|| '<br>' || fo.sellday
|| '<br>中央値/データ数 : ' || CASE WHEN fo.count2 > 0 THEN fo.median || '/' || fo.count2 ELSE 'NODATE' END
ゲームタイトル
*/
,'<a href="./usersql_exec.php?sql_id={sql_id}&pov_top5=true&direct=true&scenario_kind=scenario_all&illust_kind=illust_all&music_kind=music_all&brand_id=' || gl.brandname || '&user_id={user_id}" target="_blank">★</a> '
|| '<a href="brand.php?brand=' || bl.id || '" target="_blank">' || bl.brandname || '</a>' || CASE WHEN bl.kind = 'CIRCLE' THEN '<span class="brand_circle">(同人)</span>' ELSE '<span class="brand_corp"></span>' END
|| '<br><a href="./usersql_exec.php?sql_id={sql_id}&pov_top5=true&direct=true&scenario_kind=scenario_all&illust_kind=illust_all&music_kind=music_all&game_id=' || gl.id || '&user_id={user_id}" target="_blank">★</a> '
|| COALESCE(セール中, '')
|| CASE
WHEN uv.play= 't' THEN '<img class="user_game_status" src="/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/sub_black_accept.png">'
WHEN uv.possession = 't' THEN '<img class="user_game_status" src="/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/archive.png">'
WHEN uv.before_purchase_will != '' THEN '<img class="user_game_status" src="/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/shopping_cart.png">'
ELSE ''
END
|| '<a href="game.php?game=' || gl.id || '" target="_blank">' || gl.gamename || '</a>'
|| CASE WHEN gl.model = 'PC' THEN CASE WHEN gl.erogame = true THEN '' ELSE '(非18禁)' END ELSE '(' || gl.model || CASE WHEN gl.erogame = true THEN '' ELSE '/非18禁' END || ')' END
|| '<br>' || gl.sellday
|| '<br>中央値/データ数 : ' || CASE WHEN gl.count2 > 0 THEN COALESCE(gl.median, 0) || '/' || gl.count2 ELSE 'NODATA' END ゲームタイトル
,REPLACE(sh.shubetu_detail_name, '、', '<br>') 曲名
FROM
shokushu sh
LEFT OUTER JOIN gamelist gl ON sh.game = gl.id
INNER JOIN brandlist bl ON gl.brandname = bl.id
LEFT OUTER JOIN (SELECT gl.id
, COALESCE(
{amazon_img_line} (/*Amazon*/SELECT replace(al.mediumimage, '_SL160_', '_SL200_') FROM amazon_game AS ag, amazonlist AS al WHERE ag.asin = al.asin AND ag.game = gl.id AND al.mediumimage IS NOT NULL ORDER BY al.asin LIMIT 1),
(/*DMM*/'https://pics.dmm.co.jp/'||gl.dmm_genre||'/pcgame/'||gl.dmm||'/'||gl.dmm||'p'||CASE WHEN gl.dmm_genre_2 = 'doujin' THEN 't' ELSE 's' END||'.jpg')
, (/*DLsite*/'https://img.dlsite.jp/modpub/images2/work/'||CASE WHEN gl.dlsite_domain = 'pro' THEN 'professional' WHEN gl.dlsite_domain = 'pro2' OR left(gl.dlsite_id, 2) = 'VJ' THEN 'professional' ELSE 'doujin' END||'/'||left(gl.dlsite_id, 2)||right('000000'||(ceil(right(gl.dlsite_id, 6)::real / 1000) * 1000)::text, 6)||'/'||gl.dlsite_id||'_img_sam.jpg')
, (/*DiGiket*/'https://img.digiket.net/cg/' || to_number(substring(gl.digiket, 4,3),'999') || '/'||gl.digiket||'_2.jpg')
, (/*Gyutto*/'https://gyutto.com/data/item_img/'||left(gl.gyutto_id, -2)||'/'||gl.gyutto_id||'/'||gl.gyutto_id||'_p_m2.jpg')
, (/*Getchu*/'https://image.getchu.com/afimage/'||floor(gl.comike::real / 1000)::text||'000/'||gl.comike||'/afimg_'||gl.comike||'_120.jpg')
, '') AS mediumimage
, COALESCE(
(/*DLsite*/'https://img.dlsite.jp/modpub/images2/work/'||CASE WHEN gl.dlsite_domain = 'pro' THEN 'professional' WHEN gl.dlsite_domain = 'pro2' OR left(gl.dlsite_id, 2) = 'VJ' THEN 'professional' ELSE 'doujin' END||'/'||left(gl.dlsite_id, 2)||right('000000'||(ceil(right(gl.dlsite_id, 6)::real / 1000) * 1000)::text, 6)||'/'||gl.dlsite_id||'_img_sam.jpg')
, (/*DMM*/'https://pics.dmm.co.jp/'||gl.dmm_genre||'/pcgame/'||gl.dmm||'/'||gl.dmm||'p'||CASE WHEN gl.dmm_genre_2 = 'doujin' THEN 't' ELSE 's' END||'.jpg')
, (/*DiGiket*/'https://img.digiket.net/cg/' || to_number(substring(gl.digiket, 4,3),'999') || '/'||gl.digiket||'_2.jpg')
, (/*Gyutto*/'https://gyutto.com/data/item_img/'||left(gl.gyutto_id, -2)||'/'||gl.gyutto_id||'/'||gl.gyutto_id||'_p_m2.jpg')
, (/*Getchu*/'https://image.getchu.com/afimage/'||floor(gl.comike::real / 1000)::text||'000/'||gl.comike||'/afimg_'||gl.comike||'_120.jpg')
, '') AS doujin_image
FROM gamelist AS gl
) AS foo ON foo.id = gl.id
----------------- 最終セール時価格
LEFT OUTER JOIN (
SELECT sale.game, sale.e
,CASE WHEN sale.e < CURRENT_TIMESTAMP THEN '<span class="not_sale_now">最終セール時: ' || sale.c || ' ' || TO_CHAR(sale.e, 'YYYY-MM-DD') || '</span>' ELSE '<span class="now_on_sale"><img src="./sale.png" class="sale_icon" title="' || sale.name || '" alt="' || sale.name || '"> ' || sale.c || '<span class="text-nowrap">～' || TO_CHAR(sale.e, 'MM-DD HH24:MI') || '</span></span>' END セール時最終
,CASE WHEN sale.e < CURRENT_TIMESTAMP THEN '' ELSE '<img src="./sale.png" class="sale_icon" title="' || sale.name || '" alt="' || sale.name || '">' END セール中
,sale.rownum
FROM (
SELECT sl.name, game,end_timestamp as e, cg.content as c, ROW_NUMBER() OVER (PARTITION BY game ORDER BY end_timestamp DESC, cl.id DESC) rownum
FROM campaignlist cl INNER JOIN campaign_game cg ON cg.campaign=cl.id INNER JOIN storelist sl ON cl.store = sl.id ) sale
WHERE sale.rownum=1) ls ON ls.game = gl.id
-----------------
LEFT OUTER JOIN userreview uv ON gl.id = uv.game AND uv.uid = '{user_id}'
WHERE
sh.creater = {SQL_CREATOR_ID}
AND sh.shubetu = 6
{SQL_YEAR_LINE} AND gl.sellday BETWEEN '{SQL_YEAR_FROM}-01-01' AND '{SQL_YEAR_TO}-12-31'
ORDER BY
gl.sellday DESC
,gl.id
</SQL4>
*/