Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
MysqlでSQL1文でランキング上位を取得
select rank,base.shain_number,base.name_kanji,base.uriage FROM
(select uriage,sumrot, @rank as rank ,cnt,@rank := @rank + cnt
From (select @rank := 1) as dummy,
(select ur2.kingaku as uriage,ur2.rot as sumrot,count(*) as cnt
from uriage ur2
where ur2.year = 2016 and ur2.term = 1
and ur2.rot >= 10
group by ur2.kingaku,rot order by ur2.kingaku desc,rot desc) as grpby
) as Ranking
join
(select sh.shain_number,sh.name_kanji,ur.kingaku as uriage,ur.rot
from uriage ur
inner join shain sh on sh.shain_number = ur.shain_number
where ur.year = 2016 and ur.term = 1
and ur.rot >= 10
) as base
on base.uriage = Ranking.uriage and base.rot = Ranking.sumrot
where rank <= 3
order by rank desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment