DMM遊び放題の状況を調べに来ました
SELECT
DISTINCT bra.id bid
,'<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||bra.id||'">'||bra.brandname||'</A>' ブランド名
,CASE
WHEN bra.lost = 't' THEN '解散'
ELSE '-'
END 解散？
,bra.median ブランド中央値,cm 作品数,cdmm 遊び放題加入作品数
,'<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||t2.gid||'">'||t2.d_gname||'</A>' 代表作
,t2.d_acou 代表作プレイ数
,t2.d_med 代表作中央値
,t2.d_sell 代表作発売日
,'<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||t4.gid||'">'||t4.n_gname||'</A>' 最新作
,t4.n_acou 最新作プレイ数
,t4.n_med 最新作中央値
,t4.n_sell 最新作発売日
FROM
brandlist bra
INNER JOIN
(
SELECT
tou.brand_id bid
,tou.brandname bname
,tou.game_id gid
,tou.gamename d_gname
,tou.allcount d_acou
,tou.median d_med
,tou.sellday d_sell
FROM
toukei_temp_table tou
INNER JOIN
(
SELECT
brandname bname
,MAX(tou.allcount) cmax
FROM
toukei_temp_table tou
WHERE
tou.allcount >=10
GROUP BY
brandname
) t1
ON tou.brandname = t1.bname
WHERE
tou.allcount = t1.cmax
) t2
ON bra.id = t2.bid
INNER JOIN
(
SELECT
game.id gid
,game.brandname bid
,game.gamename n_gname
,tou.allcount n_acou
,tou.median n_med
,game.sellday n_sell
,cdmm,cm
FROM
gamelist game
LEFT OUTER JOIN toukei_temp_table tou
ON game.id = tou.game_id
INNER JOIN
(
SELECT
brandname bid
,MAX(sellday) smax,count(dmm_subsc)cdmm,count(median)cm
FROM
gamelist game
GROUP BY
brandname
) t3
ON game.brandname = t3.bid
WHERE
game.sellday = t3.smax
) t4
ON bra.id = t4.bid
where cdmm>0
ORDER BY cdmm DESC,t2.d_sell DESC