use ruwiki_p;

select count(c.domain), c.domain from

(select concat('* [', case substr(substr(substr(el_to, locate('/', el_to)), 3), 1, locate('/', substr(substr(el_to, locate('/', el_to)), 3)))

when '' then substr(el_to, 8)

else substr(substr(substr(el_to, locate('/', el_to)), 3), 1, locate('/', substr(substr(el_to, locate('/', el_to)), 3)) - 1) end, ']:')

as domain,

concat('# [[', case page_namespace when 0 then '' when 104 then 'Проект' else page_namespace end, ':',

       replace(page_title, '_', ' '), ']] — ссылка на [', el_to, ']') as page

from categorylinks join page join externallinks

on cl_from = page_id

and el_from = page_id

where cl_to = 'Московский_метрополитен'

and cl_type = 'page'

order by domain, page asc) c

join (select concat('* [', case substr(substr(substr(el_to, locate('/', el_to)), 3), 1, locate('/', substr(substr(el_to, locate('/', el_to)), 3)))

when '' then substr(el_to, 8)

else substr(substr(substr(el_to, locate('/', el_to)), 3), 1, locate('/', substr(substr(el_to, locate('/', el_to)), 3)) - 1) end, ']:')

as domain,

concat('# [[', case page_namespace when 0 then '' when 104 then 'Проект' else page_namespace end, ':',

       replace(page_title, '_', ' '), ']] — ссылка на [', el_to, ']') as page

from categorylinks join page join externallinks

on cl_from = page_id

and el_from = page_id

where cl_to = 'Московский_метрополитен'

and cl_type = 'page'

order by domain, page asc) d

group by c.domain

order by count(c.domain) desc, c.domain asc, c.page asc