Untitled query #41015

SQL

select page_id, page_title, count(*), count(distinct initials)
from
(
select *
from
(
select page.page_id, page.page_title, pl1.pl_title, 'vs' as initials
from page
	inner join pagelinks pl1
		on  page_id = pl1.pl_from       
where
page.page_namespace=0
and page.page_is_redirect=0
and pl1.pl_title like 'V%\_S%'
  and pl1.pl_title not like '%\_%\_%'
and pl1.pl_namespace=0
  and page.page_title not like '%school'
limit 20000
) vs
union all
select *
from
(
select page.page_id, page.page_title, pl1.pl_title, 'mh' as initials
from page
	inner join pagelinks pl1
		on  page_id = pl1.pl_from       
where
page.page_namespace=0
and page.page_is_redirect=0
and pl1.pl_title like 'M%\_H%'
  and pl1.pl_title not like '%\_%\_%'
and pl1.pl_namespace=0
  and page.page_title not like '%school'
limit 30000
) mh
union all
select *
from
(
select page.page_id, page.page_title, pl1.pl_title, 'ss' as initials
from page
	inner join pagelinks pl1
		on  page_id = pl1.pl_from
       
where
page.page_namespace=0
and page.page_is_redirect=0
and pl1.pl_title like 'S%\_S%'
  and pl1.pl_title not like '%\_%\_%'
and pl1.pl_namespace=0
  and page.page_title not like '%school'
limit 30000
) ss
union all
select *
from
(
select page.page_id, page.page_title, pl1.pl_title, 'rk' as initials
from page
	inner join pagelinks pl1
		on  page_id = pl1.pl_from       
where
page.page_namespace=0
and page.page_is_redirect=0
and pl1.pl_title like 'R%\_K%'
  and pl1.pl_title not like '%\_%\_%'
and pl1.pl_namespace=0
  and page.page_title not like '%school'
limit 30000
) rk
union all
select *
from
(
select page.page_id, page.page_title, pl1.pl_title, 'ph' as initials
from page
	inner join pagelinks pl1
		on  page_id = pl1.pl_from
       
where
page.page_namespace=0
and page.page_is_redirect=0
and pl1.pl_title like 'P%\_H%'
  and pl1.pl_title not like '%\_%\_%'
and pl1.pl_namespace=0
  and page.page_title not like '%school'
limit 30000
) ph
union all
select *
from
(
select page.page_id, page.page_title, pl1.pl_title, 'dg' as initials
from page
	inner join pagelinks pl1
		on  page_id = pl1.pl_from       
where
page.page_namespace=0
and page.page_is_redirect=0
and pl1.pl_title like 'D%\_G%'
  and pl1.pl_title not like '%\_%\_%'
and pl1.pl_namespace=0
  and page.page_title not like '%school'
limit 30000
) dg
        
  
  
) u
group by page_id, page_title
having count(*)>1

order by count(distinct initials) desc
SQL

Checking query status...
