ØM+ - Quarry

SQL

use ruwiki_p;
select count(c.domain), c.domain, d.page 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'
group by domain
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
on c.domain = d.domain
order by count(c.domain) desc, c.domain asc, c.page asc
SQL

Checking query status...
