/*
select 
fg.PF_CODE as "都道府県コード",
pf.PF_NAME as "都道府県名",
case when then else 0 end 人数が一番多い国のNATION_NAME as "1位 国名",
人数が一番多い国のAMT as "1位 人数",
人数が二番目に多い国のNATION_NAME as "2位 国名",
人数が二番目に多い国のAMT as "2位 人数",
人数が三番目に多い国のNATION_NAME as "3位 国名",
人数が三番目に多い国のAMT as "3位 人数",
各都道府県毎の外国人の合計人数 as "合計人数"
from foreigner as fg
inner join prefecture as pf on pf.pf_code = fg.pf_code
inner join nationality as na on na.nation_code = fg.nation_code
where fg.nation_code not in(113)
*/

select 
fg.PF_CODE as "都道府県コード",
pf.PF_NAME as "都道府県名",
--case when fg.amt = (select amt from foreigner group by amt order by amt desc limit 1,1) then na.nation_name else 0 end as "1位 国名"
fg1.nation_code as "1位 国名",
fg1.amt as "1位 人数"/*,
人数が二番目に多い国のNATION_NAME as "2位 国名",
人数が二番目に多い国のAMT as "2位 人数",
人数が三番目に多い国のNATION_NAME as "3位 国名",
人数が三番目に多い国のAMT as "3位 人数",
各都道府県毎の外国人の合計人数 as "合計人数"*/
from foreigner as fg
inner join prefecture as pf on pf.pf_code = fg.pf_code
inner join nationality as na on na.nation_code = fg.nation_code
inner join foreigner as fg1 on fg1.pf_code = fg.pf_code and fg1.amt = (select amt from foreigner group by amt order by amt desc limit 0,1) and fg2.nation_code not in(113)
inner join foreigner as fg2 on fg2.pf_code = fg.pf_code and fg2.amt = (select amt from foreigner group by amt order by amt desc limit 1,1) and fg2.nation_code not in(113)

/*
select * from foreigner
where amt =  (select amt from foreigner group by amt order by amt desc limit 0,1)
order by amt desc
*/