セール中ゲーム一覧 ver2.6.6の実行結果

SQLの説明

現在セール中のゲームソフトを中央値順に並べ、２０１８年以降のセール情報と合わせて表示します。
最安値表記は全期間・全サイトでの税変更前・価格改定前を含みます
利用方法１：現在開催中の１つのセール情報を表示したい場合、コンボボックスからセールを１つ選び、検索をします。
利用方法２：現在開催中のすべてのセール情報を表示したい場合、セール中を全部検索にチェックをつけて検索します。
利用方法３：最小中央値・データ数で絞り込む際に、発売前のゲームを含む場合はデータ数0を含むにチェックをつけます。
おまけ：ログインユーザIDを入力して検索すると、所有ゲームに目印がつき、表示・非表示を切り替えれるようになります。

検索結果

実行したSQL

      SELECT NULL DUMMY

/**
<SQL1>
    DISTINCT ' ' "<span id=""auto-number"">No</span>"
    ,CASE 
         WHEN bl.kind = 'CIRCLE' THEN COALESCE(
             '<img class="j-image"" src="' || (/*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') || '" width="125px">'
             ,'<img class="j-image"" src="' || (/*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) || '" width="125px">'
             , '<img class="j-image" src="https://pics.dmm.co.jp/digital/pcgame/' || gl.dmm || '/' || gl.dmm || 'ps.jpg" width="125px">')
         ELSE COALESCE('<img class="j-image" src="https://pics.dmm.co.jp/digital/pcgame/' || gl.dmm || '/' || gl.dmm || 'ps.jpg">','<img class="j-image" src="https://img.dlsite.jp/modpub/images2/work/professional/VJ' || TO_CHAR(TO_NUMBER(SUBSTR(gl.dlsite_id, 3, 3), '999') + 1, 'FM000') || '000/' || gl.dlsite_id ||'_img_main.jpg" width="125px">')
     END 画像
    ,'<span id="info-' || gl.id || '" class="info_all sell_store ' 
    || CASE WHEN gl.gyutto_id IS NOT NULL THEN 'store_gyutto ' ELSE '' END 
    || CASE WHEN gl.dmm IS NOT NULL OR gl.dmm_subsc IS NOT NULL THEN 'store_fanza ' ELSE '' END 
    || CASE WHEN gl.dlsite_id IS NOT NULL THEN 'store_dlsite ' ELSE '' END 
    || CASE WHEN gl.digiket IS NOT NULL THEN 'store_digiket ' ELSE '' END 
    || CASE WHEN gl.model = 'PC' THEN 'model_pc ' ELSE '' END 
    || '"></span>'
    || '<a href="./usersql_exec.php?sql_id={sql_id}&pov_top5=true&direct=true{link_with_creator}&brand_id=' || gl.brandname || '&user_id={user_id}" target="_blank">★</a> <a class="j-brand-' || gl.brandname || '" href="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand=' || gl.brandname || '" target="_blank">' 
    || bl.brandname || CASE WHEN bl.kind = 'CIRCLE' THEN '<span class="brand_circle">(同人)</span>' ELSE '<span class="brand_corp"></span>' END || '</a><br>'
    || '<a href="./usersql_exec.php?sql_id={sql_id}&pov_top5=true&direct=true{link_with_creator}&game_id=' || gl.id || '&user_id={user_id}" target="_blank">★</a> ' 
    || CASE 
        WHEN play= 't' THEN '<img class="j-possesionOrPlay" src="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/sub_black_accept.png" style="height:1em;">'
        WHEN possession = 't' THEN '<img class="j-possesionOrPlay" src="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/archive.png" style="height:1em;">'
        WHEN uv.before_purchase_will != '' THEN '<img src="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/img/primo_icons/48x48/shopping_cart.png" style="height:1em;">'
        ELSE ''
    END
    || '<a href="/~ap2/ero/toukei_kaiseki/game.php?game=' || gl.id || '" target="_blank">' 
    || gl.gamename || 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 || '</a><br>'
    || gl.sellday || '<br>'
    {with_median} 
    || COALESCE(gl.genre, '') 
    || CASE WHEN gl.erogame = true THEN '<br>' || CASE WHEN gl.okazu = true THEN '<span class="tag okazu_flag okazu_true">抜き</span>' ELSE  '<span class="tag okazu_flag okazu_false">非抜き</span>' END || CASE gl.axis_of_soft_or_hard WHEN 0 THEN '/<span class="tag ero_kind ero_normal">どちらともいえない</span>' WHEN 1 THEN '/<span class="tag ero_kind ero_soft">和姦</span>' WHEN -1 THEN '/<span class="tag ero_kind ero_hard">凌辱</span>' ELSE  '<span class="ero_kind ero_unknown"></span>' END ELSE '<span class="okazu_flag okazu_false ero_kind ero_none"></span>' END
    || CASE 
           WHEN gl.dmm_sample_image_count <= 0 AND gl.dlsite_sample_image_count <= 0 AND gl.gyutto_sample_image_count <= 0 AND gl.digiket_sample_image_count <= 0 THEN ''
           ELSE '<br>サンプルCG ['
               || CASE WHEN gl.dmm_sample_image_count > 0 THEN  '<a href="javascript:void(0);" onclick="saleSearch.popupSample(''FANZA'', ''/~ap2/ero/toukei_kaiseki/game_dmm.php?game=' || gl.id || ''');">FANZA(' || gl.dmm_sample_image_count || ')</a>' ELSE '' END
               || CASE WHEN gl.dlsite_sample_image_count > 0 THEN  ' <a href="javascript:void(0);"  onclick="saleSearch.popupSample(''DLsite.com'', ''/~ap2/ero/toukei_kaiseki/game_dlsite.php?game=' || gl.id || ''');">DLsite.com(' || gl.dlsite_sample_image_count || ')</a>' ELSE '' END
               || CASE WHEN gl.gyutto_sample_image_count > 0 THEN  ' <a href="javascript:void(0);"  onclick="saleSearch.popupSample(''Gyutto'', ''/~ap2/ero/toukei_kaiseki/game_gyutto.php?game=' || gl.id || ''');">Gyutto(' || gl.gyutto_sample_image_count || ')</a>'  ELSE ''  END
--               || CASE WHEN gl.digiket_sample_image_count > 0 THEN  ' <a href="/~ap2/ero/toukei_kaiseki/game_digiket.php?game=' || gl.id || '"  target="_blank">DiGiket(' || gl.digiket_sample_image_count || ')</a>' ELSE ''  END
       END
    || CASE WHEN gl.dmm_sample_image_count <= 0 AND gl.dlsite_sample_image_count <= 0 AND gl.gyutto_sample_image_count <= 0 AND gl.digiket_sample_image_count <= 0 THEN '' ELSE ']' END
    || '<br>Link [' ||COALESCE('<a href="' || gl.shoukai || '" target="_blank">OHP</a> ', '')
    || CASE
           WHEN bl.kind = 'CIRCLE' THEN COALESCE('<a href="https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=' || gl.dmm || '/" target="_blank">FANZA同人</a> ', '')
           ELSE ''
       END
    || COALESCE('<a href="https://dlsoft.dmm.co.jp/detail/' || gl.dmm || '/" target="_blank">FANZA</a>', '')
    || COALESCE(' <a href="https://www.dlsite.com/' || gl.dlsite_domain || '/work/=/product_id/' || gl.dlsite_id || '.html" target="_blank">DLSite</a>', '')
    || COALESCE(' <a href="https://www.digiket.com/work/show/_data/ID=' || gl.digiket || '/" target="_blank">DiGiket</a>', '')
    || COALESCE(' <a href="http://gyutto.com/i/item' || gl.gyutto_id || '" target="_blank">Gyutto</a>', '')
--    || COALESCE(' <a href="https://www.amazon.co.jp/exec/obidos/ASIN/' || az.asin || '/"  target="_blank">Amazon</a>', '')
    || CASE
           WHEN trial_h = true THEN COALESCE(' <a class="trial_kind trial_has" href="' || gl.trial_url || '"  target="_blank">体験版(H有)</a>', '<span class="trial_kind trial_none"></span>')
           ELSE COALESCE(' <a class="trial_kind trial_has" href="' || gl.trial_url || '"  target="_blank">体験版</a>', '<span class="trial_kind trial_none"></span>')
       END
    || ']'
    || '<br>プレイ時間中央値 ' || COALESCE(gl.total_play_time_median, 0) || 'h'
      タイトル
     ,CASE
         WHEN uv.before_purchase_will = '' THEN '' 
         WHEN uv.before_purchase_will = '0_必ず購入' THEN '<span class="j-fav" data-order="1">必ず購入</span>'
         WHEN uv.before_purchase_will = '多分購入' THEN '<span class="j-fav" data-order="2">多分購入</span>'
         WHEN uv.before_purchase_will = '様子見' THEN '<span class="j-fav" data-order="3">様子見</span>'
         ELSE '<span class="j-fav" order="4">' || uv.before_purchase_will || '</span>'
     END "<span id=""before_purchase_will_head"">予定</span>"
     ,CASE 
         WHEN gl.dmm_subsc IS NULL AND og.game IS NULL THEN '<span class="subsc_none"></span>'
         WHEN gl.dmm_subsc IS NULL THEN '<span class="subsc_oops subsc_has"><a href="https://oo.parts/title/' || og.ooparts || '" target="_blank">OOParts</a></span>'
         WHEN og.game IS NULL THEN '<span class="subsc_dmm subsc_has"><a href=https://dlsoft.dmm.co.jp/subsc/li/?q=' || gl.gamename  || ' target="_blank">DMM</a></span>'
         ELSE '<span class="subsc_dmm subsc_oops subsc_has"><a href="https://dlsoft.dmm.co.jp/subsc/li/?q=' || gl.gamename  || '" target="_blank">DMM</a><br><a href="https://oo.parts/title/' || og.ooparts || '" target="_blank">OOParts</a></span>'
     END  "<span id=""subsc_head"">サブスク</span>"
     ,COALESCE(gl.median, 0) 中央値
     ,COALESCE(gl.count2, 0) データ数
     ,CASE WHEN mcg.price != 99999 THEN '過去全セール最安：' || mcg.price || '円<br>' ELSE '' END || 価格 価格
     ,過去価格2018年以降
 FROM campaignlist cl INNER JOIN campaign_game cg ON cl.id = cg.campaign AND cl.id = {campaignlist_id}
     INNER JOIN gamelist gl ON cg.game = gl.id
     INNER JOIN brandlist bl ON gl.brandname =bl.id
     INNER JOIN 
 (SELECT
     gl.id
     ,gl.gamename
     ,'<div class="position_reset now_on_sale_list">' || STRING_AGG('<span class="tag now_on_sale discount_target' 
                                                                                                       || CASE sl.name WHEN 'FANZA' THEN ' sale_fanza' WHEN 'DLsite.com' THEN ' sale_dlsite' WHEN 'DiGiket.com' THEN ' sale_digiket' ELSE ' sale_other' END
                                                                                                       || '" ' -- 割引セール店CLASSの追加 
        || CASE WHEN (regexp_match(cg.content,'...OFF'))[1] IS NOT NULL THEN 'data-discount="' || replace(replace((regexp_match(cg.content,'...OFF'))[1],'%OFF',''),'％OFF','') || '"'  ELSE '' END
        || ' data-gameid="' || gl.id || '"'
        || ' data-price="' ||
        -- ゲーム価格計算開始
        CASE
            WHEN cg.content = '10本で9,500円/5本で5,000円/3本で3,000円'    THEN 950
            WHEN cg.content = '最大5タイトルで10,000円'    THEN 2000
            WHEN cg.content = '7,800円→7,020 10%OFF円'     THEN 7020
            WHEN cg.content LIKE '5,141円→30\%OFF%'        THEN 3598
            WHEN cg.content LIKE '%→%円%'                  THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content,'→') + 1, STRPOS(SUBSTR(cg.content,STRPOS(cg.content,'→') + 1),'円')), '円, ', '') AS INTEGER)
            WHEN cg.content LIKE '%→50\%OFF%'              THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ',','') AS INTEGER) / 2
            WHEN cg.content LIKE '%→% %OFF%'               THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
            WHEN cg.content LIKE '%→%\%OFF%'               THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content,'→') + 1, STRPOS(SUBSTR(cg.content,STRPOS(cg.content,'→') + 1),'円')), '円, ', '') AS INTEGER)
            WHEN cg.content LIKE '%→%'                     THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content,'→') + 1), ' ,円', '') AS INTEGER)
            WHEN cg.content LIKE '%本で%\%OFF%/ %円%\%OFF%' THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content, '/') + 2 ,STRPOS(cg.content, '円') - STRPOS(cg.content, '/')), '円, ', '') AS INTEGER)
            WHEN cg.content LIKE '%円 %\%OFF%'              THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), '～ものべの全部入り通常版円, ', '') AS INTEGER)
            WHEN cg.content LIKE '%から300円OFF'            THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content,'円')), ',','') AS INTEGER) - 300
            WHEN cg.content LIKE '%円～ 最大%\%OFF%'        THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
            WHEN cg.content LIKE '%円～ %\%OFF%'        THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
            WHEN cg.content LIKE '%円% %\%OFF%'        THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
            WHEN cg.content LIKE '%本以上購入でで%％OFF%'         THEN 99999
            WHEN cg.content LIKE '%本購入で%\%OF'           THEN 99999
            WHEN cg.content LIKE '% %\%OFF%'                THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, ' ')), ', ', '') AS INTEGER)
            WHEN cg.content LIKE '%本以上で%％OFF%'         THEN 99999
            WHEN cg.content LIKE '%本以上で%\%OFF%'         THEN 99999
            WHEN cg.content LIKE '%本で%\%OFF%'            THEN 99999
            WHEN cg.content LIKE '%本%\%OFF%'               THEN 99999
            WHEN cg.content LIKE '%本%'
                THEN CAST(
                         TRANSLATE(
                             SUBSTR(REPLACE(cg.content, '万', '0000')
                                 ,STRPOS(cg.content,'本') + 1
                                 ,STRPOS(REPLACE(cg.content, '万', '0000'),'円') - STRPOS(cg.content,'本')
                             )
                             ,'セット,，選んで円大まとめて', ''
                         ) AS INTEGER
                     ) / 
                     CAST(
                         TRANSLATE(
                             (REGEXP_MATCH(cg.content,'..?本'))[1]
                             ,'得本大'
                             , ''
                         ) AS INTEGER
                     )
            WHEN cg.content = '「ワルキューレロマンツェ」シリーズ4タイトルで9,933円'    THEN 2483
            WHEN cg.content = '3タイトルで2,980円対象ゲーム'                            THEN 993
            WHEN cg.content LIKE '%円%'                                                 THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content,'円')), 'キャンペーン価格　【半額】単品販売価格 円,', '') AS INTEGER)
            ELSE '99999'
        END
        -- ゲーム価格計算終了
        || '">' || sl.name || '</span>:<a href="' || cl.url || '" target="_blank"><span>' || cl.name || '</span><span class="popup_ts">' || cl.name || ' : '  || cl.end_timestamp || '</span></a><strong>' 
        || CASE sl.name
           WHEN 'FANZA' THEN '<a href="https://dlsoft.dmm.co.jp/detail/' || gl.dmm || '/" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           WHEN 'DLsite.com' THEN'<a href="https://www.dlsite.com/' || gl.dlsite_domain || '/work/=/product_id/' || gl.dlsite_id || '.html" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           WHEN 'DiGiket.com' THEN'<a href="https://www.digiket.com/work/show/_data/ID=' || gl.digiket || '/" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           WHEN 'Gyutto.com' THEN'<a href="http://gyutto.com/i/item' || gl.gyutto_id || '" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           ELSE cg.content
           END 
        ,'</strong><br>' ORDER BY cl.end_timestamp DESC, cl.id DESC) || '</div>' 価格
 FROM
     campaign_game cg
     INNER JOIN gamelist gl ON cg.game = gl.id 
     INNER JOIN campaignlist cl ON cg.campaign = cl.id AND  cl.start_timestamp <= CURRENT_TIMESTAMP AND cl.end_timestamp >= CURRENT_TIMESTAMP
     INNER JOIN storelist sl ON cl.store = sl.id
 GROUP BY
     gl.id
     ,gl.gamename) pr ON gl.id = pr.id
     INNER JOIN 
 (SELECT
     gl.id
     ,gl.gamename
     ,'<input type="button" value="過去価格表示" onclick="togglePrice(this, ''price-' || gl.id || ''');"><div id="price-' || gl.id || '" class="position_reset" style="display:none;">' || STRING_AGG(CASE WHEN cl.end_timestamp < CURRENT_TIMESTAMP THEN sl.name ELSE '<span class="tag now_on_sale">' || sl.name || '</span>' END || ':<a href="' || cl.url || '" target="_blank"><span>' || cl.name || '</span><span class="popup_ts">' || cl.name || ' : '  || cl.end_timestamp || '</span></a><strong>' 
        || CASE sl.name
           WHEN 'FANZA' THEN '<a href="https://dlsoft.dmm.co.jp/detail/' || gl.dmm || '/" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           WHEN 'DLsite.com' THEN'<a href="https://www.dlsite.com/' || gl.dlsite_domain || '/work/=/product_id/' || gl.dlsite_id || '.html" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           WHEN 'DiGiket.com' THEN'<a href="https://www.digiket.com/work/show/_data/ID=' || gl.digiket || '/" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           WHEN 'Gyutto.com' THEN'<a href="http://gyutto.com/i/item' || gl.gyutto_id || '" target="_blank" style="color:#ff4500">' || cg.content || '</a>'
           ELSE cg.content
           END 
        , '</strong><br>' ORDER BY cl.end_timestamp DESC, cl.id DESC) || '</div>' 過去価格2018年以降
 FROM
     campaign_game cg
     INNER JOIN gamelist gl ON cg.game = gl.id
         AND (
                 (gl.count2 >= {min_datacount} AND gl.median BETWEEN {min_median} AND {max_median})
{include_zero}             OR (COALESCE(gl.count2, 0) = 0 OR COALESCE(gl.median, 0) = 0)
         )
     INNER JOIN campaignlist cl ON cg.campaign = cl.id AND  cl.start_timestamp >= '2018-01-01' 
     INNER JOIN storelist sl ON cl.store = sl.id
 GROUP BY
     gl.id
     ,gl.gamename) pr2 ON gl.id = pr2.id
     LEFT OUTER JOIN ooparts_game og ON gl.id = og.game
     LEFT OUTER JOIN userreview uv ON uv.game= gl.id AND uv.uid = '{user_id}'
     LEFT OUTER JOIN (SELECT cg.game, MIN(CASE
                WHEN cg.content = '10本で9,500円/5本で5,000円/3本で3,000円'    THEN 950
                WHEN cg.content = '最大5タイトルで10,000円'    THEN 2000
                WHEN cg.content = '7,800円→7,020 10%OFF円'     THEN 7020
                WHEN cg.content LIKE '5,141円→30\%OFF%'        THEN 3598
                WHEN cg.content LIKE '%→%円%'                  THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content,'→') + 1, STRPOS(SUBSTR(cg.content,STRPOS(cg.content,'→') + 1),'円')), '円, ', '') AS INTEGER)
                WHEN cg.content LIKE '%→50\%OFF%'              THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ',','') AS INTEGER) / 2
                WHEN cg.content LIKE '%→% %OFF%'               THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
                WHEN cg.content LIKE '%→%\%OFF%'               THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content,'→') + 1, STRPOS(SUBSTR(cg.content,STRPOS(cg.content,'→') + 1),'円')), '円, ', '') AS INTEGER)
                WHEN cg.content LIKE '%→%'                     THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content,'→') + 1), ' ,円', '') AS INTEGER)
                WHEN cg.content LIKE '%本で%\%OFF%/ %円%\%OFF%' THEN CAST(TRANSLATE(SUBSTR(cg.content, STRPOS(cg.content, '/') + 2 ,STRPOS(cg.content, '円') - STRPOS(cg.content, '/')), '円, ', '') AS INTEGER)
                WHEN cg.content LIKE '%円 %\%OFF%'              THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), '～ものべの全部入り通常版円, ', '') AS INTEGER)
                WHEN cg.content LIKE '%から300円OFF'            THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content,'円')), ',','') AS INTEGER) - 300
                WHEN cg.content LIKE '%円～ 最大%\%OFF%'        THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
                WHEN cg.content LIKE '%円～ %\%OFF%'        THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
                WHEN cg.content LIKE '%円% %\%OFF%'        THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, '円')), ', ', '') AS INTEGER)
                WHEN cg.content LIKE '%本以上購入でで%％OFF%'         THEN 99999
                WHEN cg.content LIKE '%本購入で%\%OF'           THEN 99999
                WHEN cg.content LIKE '% %\%OFF%'                THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content, ' ')), ', ', '') AS INTEGER)
                WHEN cg.content LIKE '%本以上で%％OFF%'         THEN 99999
                WHEN cg.content LIKE '%本以上で%\%OFF%'         THEN 99999
                WHEN cg.content LIKE '%本で%\%OFF%'            THEN 99999
                WHEN cg.content LIKE '%本%\%OFF%'               THEN 99999
                WHEN cg.content LIKE '%本%'
                    THEN CAST(
                             TRANSLATE(
                                 SUBSTR(REPLACE(cg.content, '万', '0000')
                                     ,STRPOS(cg.content,'本') + 1
                                     ,STRPOS(REPLACE(cg.content, '万', '0000'),'円') - STRPOS(cg.content,'本')
                                 )
                                 ,'セット,，選んで円大まとめて', ''
                             ) AS INTEGER
                         ) / 
                         CAST(
                             TRANSLATE(
                                 (REGEXP_MATCH(cg.content,'..?本'))[1]
                                 ,'得本大'
                                 , ''
                             ) AS INTEGER
                         )
                WHEN cg.content = '「ワルキューレロマンツェ」シリーズ4タイトルで9,933円'    THEN 2483
                WHEN cg.content = '3タイトルで2,980円対象ゲーム'                            THEN 993
                WHEN cg.content LIKE '%円%'                                                 THEN CAST(TRANSLATE(SUBSTR(cg.content, 0,STRPOS(cg.content,'円')), 'キャンペーン価格　【半額】単品販売価格 円,', '') AS INTEGER)
                ELSE 99999
            END) price
        FROM campaign_game cg GROUP BY cg.game) mcg ON cg.game = mcg.game
 ORDER BY 
     {custom_order}
     COALESCE(gl.median, 0) DESC
     ,COALESCE(gl.count2, 0) DESC
 LIMIT {select_limit}
</SQL1>
<SQL2>
    '<option value="' || cl.id || '">' 
    ||  CASE CAST(cl.end_timestamp AS DATE) - CURRENT_DATE
            WHEN 0 THEN '【本日終了】 '
            WHEN 1  THEN '【@1日】 '
            WHEN 2  THEN '【@2日】 '
            WHEN 3  THEN '【@3日】 '
            WHEN 4  THEN '【@4日】 '
            WHEN 5  THEN '【@5日】 '
            WHEN 6  THEN '【@6日】 '
            WHEN 7  THEN '【@7日】 '
            ELSE ''
        END 
    || cl.id || ':' || sl.name || ': ' || cl.name || ' 【~' || TO_CHAR(cl.end_timestamp, 'YYYY-MM-DD HH24:MI') || '】'
--    || CASE WHEN cl.end_timestamp - CURRENT_TIMESTAMP <= '7 days' 
--                  THEN '@' || date_trunc('day', cl.end_timestamp - CURRENT_TIMESTAMP) || ' '
--                  ELSE ''
--       END 
    || '</option>' list
FROM
    campaignlist cl 
    INNER JOIN storelist sl ON cl.store = sl.id
    INNER JOIN campaign_game cg ON cg.campaign = cl.id
WHERE
     CURRENT_TIMESTAMP BETWEEN cl.start_timestamp AND cl.end_timestamp
GROUP BY
    cl.id
    ,'<option value="' || cl.id || '">' 
    ||  CASE CAST(cl.end_timestamp AS DATE) - CURRENT_DATE
            WHEN 0 THEN '【本日終了】 '
            WHEN 1  THEN '【@1日】 '
            WHEN 2  THEN '【@2日】 '
            WHEN 3  THEN '【@3日】 '
            WHEN 4  THEN '【@4日】 '
            WHEN 5  THEN '【@5日】 '
            WHEN 6  THEN '【@6日】 '
            WHEN 7  THEN '【@7日】 '
            ELSE ''
        END 
    || cl.id || ':' || sl.name || ': ' || cl.name || ' 【~' || TO_CHAR(cl.end_timestamp, 'YYYY-MM-DD HH24:MI') || '】'
    || '</option>'
ORDER BY
    cl.id DESC
</SQL2>

**/