use trwiki_p;

SET sql_mode='PIPES_AS_CONCAT';

select '# [['||el_to ||']]' as el_to, CONCAT('# ',GROUP_CONCAT(page_title SEPARATOR ' '),'') as page, COUNT(el_to) as cnt

from externallinks 

  inner join page

    on (page_id=el_from)

where page_namespace =0

and (el_to like '%.blogspot.%'  

  or el_to like '%.tumblr.%' 

  or el_to like '%geocities.%' 

  or el_to like '%.livejournal.%'

  or el_to like '%.tr.gg%'

  or el_to like '%.wordpress.com%'

  or el_to like '%.blogcu.com%'

  or el_to like '%.weebly.com%'

  or el_to like '%.net.tc%'

  or el_to like '%.co.cc%'

  or el_to like '%.azbuz.com%'

  or el_to like '%.somee.com%'

  or el_to like '%osmanli700.gen.tr%'

  or el_to like '%kimkimdir.gen.tr%'

  or el_to like '%ermenisorunu.gen.tr%'

  or el_to like '%canakkale.gen.tr%'

  or el_to like '%ataturk.net%'

  or el_to like '%turkcebilgi.com%'

  or el_to rlike '\.k12\.?[a-z]{0,2}/\S'

  or el_to like '%birimcevir.com%'

  or el_to like '%eksisozluk.%'

  or el_to like '%.sourtimes.%'

  or el_to like '%onedio.com%' 

  or el_to like '%.tripod.com%'

  or el_to like '%jenerator.name.tr%'

  or el_to like '%muhendisbeyinler.net%'

  or el_to LIKE '%uludagsozluk.com%'  

  or el_to LIKE '%savsatli.com%'

  or el_to LIKE '%karakocannostalji.com%'

  or el_to LIKE '%alisarkoyu.com%'

  or el_to LIKE '%akcakocam.com%'

  or el_to LIKE '%sivricehaber.com%'

  or el_to LIKE '%karamanlive.com%'

  or el_to LIKE '%sozlukspot.com%'

  or el_to LIKE '%.wikia.com%'

  or el_to LIKE '%itusozluk.com%'

  or el_to LIKE '%quora.com%'

  or el_to LIKE '%answers.com%'

  or el_to LIKE '%sindirella.com%'

  or el_to LIKE '%haberbedava.com%'

  or el_to LIKE '%ensonhaber.com%'

  or el_to LIKE '%haberler.com%'

  or el_to LIKE '%anlatio.com%'

  or el_to LIKE '%listelist.com%'

  or el_to LIKE '%bilgiustam.com%'

  or el_to like '%.8m.net%'

  or el_to like '%kimyaokulu.com%'

  or el_to like '%biyografi.info%'

  or el_to like '%blog.milliyet.com.tr%'

  or el_to like '%haberkonseyi.com%'

  or el_to like '%kuran.gen.tr%'

  or el_to like '%kochisar.gen.tr%'

  or el_to like '%siir.gen.tr%'

  or el_to like '%cografya.gen.tr%'

  or el_to like '%turkbirlik.gen.tr%'

  or el_to like '%starwars.gen.tr%'

  or el_to like '%mimarsinan.gen.tr%'

  or el_to like '%biyografisi.gen.tr%'

  or el_to like '%bluepoint.gen.tr%'

  or el_to like '%kelimeler.gen.tr%'

  or el_to like '%cerkes.gen.tr%'

  or el_to like '%commodore.gen.tr%'

  or el_to like '%kafkas.gen.tr%'

  or el_to like '%tarih.gen.tr%'

  or el_to like '%film.gen.tr%'

  or el_to like '%manga.gen.tr%'

  or el_to like '%biliyormuydunuz.gen.tr%'

  or el_to like '%biraz.gen.tr%'

  or el_to like '%folklor.gen.tr%'

  or el_to like '%tarihbilimi.gen.tr%'

  or el_to like '%canakkale.gen.tr%'

  or el_to like '%kimkimdir.net%'

  or el_to like '%cirali.gen.tr%'

  or el_to like '%istanbulspor.gen.tr%'

  or el_to like '%tst.gen.tr%'

  or el_to like '%tarihtebugun.gen.tr%'

  or el_to like '%sosyoloji.gen.tr%'

  or el_to like '%manisamanset.gen.tr%'

  or el_to like '%mustafatasar.gen.tr%'

  or el_to like '%cide.gen.tr%'

  or el_to like '%candanercetin.gen.tr%'

  or el_to like '%bilgievi.gen.tr%'

  or el_to like '%bedenegitimi.gen.tr%'

  or el_to like '%balkanincileri.gen.tr%'

  or el_to like '%balikesir.gen.tr%'

  or el_to like '%balikesir.com%'

  or el_to like '%hukukfakultesi.gen.tr%'

  or el_to like '%hihaphulusi.gen.tr%'

  or el_to like '%hraspi.gen.tr%'

  or el_to like '%hosmanlilar.gen.tr%'

  or el_to like '%horhangencebay.gen.tr%'

  or el_to like '%hnobel.gen.tr%'

  or el_to like '%hmesafe.gen.tr%'

  or el_to like '%hsayfaki.com%'

  or el_to like '%hseyfettinaslan.gen.tr%'

  or el_to like '%hmehmetakifbal.gen.tr%'

  or el_to like '%hkurucasile.gen.tr%'

  or el_to like '%hkalmun.gen.tr%'

  or el_to like '%islamiyet.gen.tr%'

  or el_to like '%drhakanozkul.gen.tr%'

  or el_to like '%su.gen.tr%'

  or el_to like '%cografyabilimi.gen.tr%'

  or el_to like '%eczaneler.gen.tr%'

  or el_to like '%islamisayfa.com%'

  or el_to like '%gorele.gen.tr%'

  or el_to like '%hukuk.gen.tr%'

  or el_to like '%idrar.gen.tr%'

  or el_to like '%%C3%A7e%C5%9Fmeler.gen.tr%'

  or el_to like '%alkol.gen.tr%'

  or el_to like '%anamur.gen.tr%'

  or el_to like '%ankararehberi.gen.tr%'

  or el_to like '%agac.gen.tr%'

  or el_to like '%afet.gen.tr%'

  or el_to like '%acikkapi.gen.tr%'

  or el_to like '%vitae.gen.tr%'

  or el_to like '%arit.gen.tr%'

  or el_to like '%antalya.gen.tr%'

  or el_to like '%aricilik.gen.tr%'

  or el_to like '%windowsxp.gen.tr%'

  or el_to like '%yarimada.gen.tr%'

  or el_to like '%yesilcam.gen.tr%'

  or el_to like '%yesilkoy.gen.tr%'

  or el_to like '%usturakemaldizisi.gen.tr%'

  or el_to like '%swim.gen.tr%'

  or el_to like '%co%C4%9Frafya.gen.tr%'

  or el_to like '%ayer.gen.tr%'

  or el_to like '%atasozleri.gen.tr%'

  or el_to like '%ayetelkursi.gen.tr%'

  or el_to like '%ayvali.gen.tr%'

  or el_to like '%bellek.gen.tr%'

  or el_to like '%bilecik.gen.tr%'

  or el_to like '%bilgiler.gen.tr%'

  or el_to like '%bulten.gen.tr%'

  or el_to like '%byenice.gen.tr%'

  or el_to like '%camiihalisi.gen.tr%'

  or el_to like '%camlihemsin.gen.tr%'

  or el_to like '%canakci.com.tr%'

  or el_to like '%canakci.gen.tr%'

  or el_to like '%canakkalesavasi.gen.tr%'

  or el_to like '%caninsaat.com.tr%'

  or el_to like '%caninsaat.gen.tr%'

  or el_to like '%cennethurmasi.gen.tr%'

  or el_to like '%cesme.gen.tr%'

  or el_to like '%ceviz.gen.tr%'

  or el_to like '%cizgiroman.gen.tr%'

  or el_to like '%dunyanin7harikasi.gen.tr%'

  or el_to like '%duman6.gen.tr%'

  or el_to like '%disimplanti.gen.tr%'

  or el_to like '%dinibilgiler.gen.tr%'

  or el_to like '%demokrasi.gen.tr%'

  or el_to like '%.tr.tc%'

  or el_to like '%e-net.gen.tr%'

  or el_to like '%edebiyatogretmeni.gen.tr%'

  or el_to like '%index.gen.tr%'

  or el_to like '%yilmaz.tc%'

  or el_to like '%ingilizceturkce.gen.tr%'

  or el_to like '%ilkayakkaya.gen.tr%'

  or el_to like '%engelliler.gen.tr%'

  or el_to like '%felsefe.gen.tr%'

  or el_to like '%etiyopyahavayollari.gen.tr%'

  or el_to like '%fecr.gen.tr%'

  or el_to like '%fokus.gen.tr%'

  or el_to like '%fransizcasozluk.gen.tr%'

  or el_to like '%galactica.gen.tr%'

  or el_to like '%balnet.net%'

  or el_to like '%haberdiyarbakir.gen.tr%'

  or el_to like '%hamidiyekoyu.gen.tr%'

  or el_to like '%hpv.gen.tr%'

  or el_to like '%hzmuhammedinhayati.gen.tr%'

  or el_to like '%kopek.gen.tr%'

  or el_to like '%kuantum.gen.tr%'

  or el_to like '%kultursanat.gen.tr%'

  or el_to like '%ipucu.gen.tr%'

  or el_to like '%iskembecorbasi.gen.tr%'

  or el_to like '%islamisohbet.gen.tr%'

  or el_to like '%istatistik.gen.tr%'

  or el_to like '%iznik.gen.tr%'

  or el_to like '%kangruplari.gen.tr%'

  or el_to like '%karsilastir.gen.tr%'

  or el_to like '%kavakli.gen.tr%'

  or el_to like '%kaybolanyillar.gen.tr%'

  or el_to like '%kizarkadas.gen.tr%'

  or el_to like '%mared.gen.tr%'

  or el_to like '%mma.gen.tr%'

  or el_to like '%kurandasevgi.gen.tr%'

  or el_to like '%kuranikerim.gen.tr%'

  or el_to like '%kurkehayir.gen.tr%'

  or el_to like '%kurtulussavasi.gen.tr%'

  or el_to like '%oyunculari.gen.tr%'

  or el_to like '%sarkisozum.gen.tr%'

  or el_to like '%manjaro.gen.tr%'

  or el_to like '%lada.gen.tr%'

  or el_to like '%matrak.gen.tr%'

  or el_to like '%mehmetcik.gen.tr%'

  or el_to like '%memekanseri.gen.tr%'

  or el_to like '%mesutyilmaz.gen.tr%'

  or el_to like '%mgz.gen.tr%'

  or el_to like '%mustafaaydin.gen.tr%'

  or el_to like '%mustafaeraslan.gen.tr%'

  or el_to like '%nazilli.gen.tr%'

  or el_to like '%nazimhikmet.gen.tr%'

  or el_to like '%ngbb.gen.tr%'

  or el_to like '%nur.gen.tr%'

  or el_to like '%ondokuz.gen.tr%'

  or el_to like '%onlinecanliradyo.gen.tr%'

  or el_to like '%osmanl%C4%B1700.gen.tr%'

  or el_to like '%psikoloji.gen.tr%'

  or el_to like '%pku.gen.tr%'

  or el_to like '%pide.gen.tr%'

  or el_to like '%picasa.gen.tr%'

  or el_to like '%pclabs.gen.tr%'

  or el_to like '%pacman.gen.tr%'

  or el_to like '%oyunsever.gen.tr%'

  or el_to like '%ova.gen.tr%'

  or el_to like '%osmanlipadisahlari.gen.tr%'

  or el_to like '%osmanlica.gen.tr%'

  or el_to like '%saanen.gen.tr%'

  or el_to like '%saatlerinanlamlari.gen.tr%'

  or el_to like '%sailormoon.gen.tr%'

  or el_to like '%saklicennet.gen.tr%'

  or el_to like '%sandikli.gen.tr%'

  or el_to like '%sarkisozum.gen.tr%'

  or el_to like '%meltem.gen.tr%'

  or el_to like '%wamp.gen.tr%'

  or el_to like '%uzunkopru.gen.tr%'

  or el_to like '%tv7.gen.tr%'

  or el_to like '%turkdili.gen.tr%'

  or el_to like '%turk-yunan.gen.tr%'

  or el_to like '%trafikisaretleri.gen.tr%'

  or el_to like '%trackmania.gen.tr%'

  or el_to like '%tosya.gen.tr%'

  or el_to like '%tefsir.gen.tr%'

  or el_to like '%sts.gen.tr%'

  or el_to like '%sporhaberleri.gen.tr%'

  or el_to like '%sosyalbilgiler.org%'

  or el_to like '%sosyalbilgiler.gen.tr%'

  or el_to like '%soma.gen.tr%'

  or el_to like '%so.gen.tr%'

  or el_to like '%sinope.gen.tr%'

  or el_to like '%sille.gen.tr%'

  or el_to like '%sgk.gen.tr%'

  or el_to like '%suryaniler.com%'

  or el_to like '%mansetistanbul.com%'

  or el_to like '%medyagunebakis.com%'

  or el_to like '%fandom.com%'

  or el_to like '%groups.google.com%'

  or el_to like '%groups.yahoo.com%'

  or el_to like '%reddit.com%'

  or el_to like '%.tk/%/'

  or el_to like '%.gq/%/'

  or el_to like '%.ml/%/'

  or el_to like '%.ga/%/'

  or el_to like '%.cf/%/'

  or el_to like '%mydictionary.dyndns.org%'

  or el_to like '%birnebun.com%'

  or el_to like '%netfirms.com%'

  or el_to like '%bilgilik.com%'

  or el_to like '%arayan.com%'

  or el_to like '%degisimmedya.com%'

  or el_to like '%kaknus.com%'

  or el_to like '%biyografi.net%'

  or el_to like '%karalahana.com%'

  or el_to like '%hemensaglik.com%'

  or el_to like '%turkuler.com%'

  or el_to like '%alternatifim.com%'

  or el_to like '%kalemguzeli.net%'

  or el_to like '%islamawareness.net%'

  or el_to like '%turkedebiyatiisimlersozlugu.com%'

  or el_to like '%ankarakalesi.com%'

  or el_to like '%porttakal.com%'

  or el_to like '%renkhaber.com%'

  or el_to like '%yasamoykusu.com%'

  or el_to like '%orgfree.com%'

  or el_to like '%edebiyatvesanatakademisi.com%'

  or el_to like '%pspciyiz.biz%'

  or el_to like '%mathforum.org%'

  or el_to like '%kickassclassical.com%'

  or el_to like '%axishistory.com%'

  or el_to like '%edebi.net%'

  or el_to like '%kolonin.org%'

  or el_to like '%turkdiliveedebiyati.com%'

  or el_to like '%deyerler.org%'

  or el_to like '%derkenar.com%'

  or el_to like '%striphaber.com%'

  or el_to like '%yemekliz.com%'

  or el_to like '%dervisan.com%'

  or el_to like '%mustinet.net%'

  or el_to like '%magazinhaberleri.com%'

  or el_to like '%medyagunebakis.com%'

  or el_to like '%egehaber.com%'

  or el_to like '%mansetistanbul.com%'

  or el_to like '%gazetevahdet.com%'

  or el_to like '%polisinsesi.com%'

  or el_to like '%demokrasihaber.com'

  or el_to like '%ciddigazete.com%'

  or el_to like '%medium.com%'

  or el_to like '%kodamcasi.com%'

  or el_to like '%mekanizmalar.com%'

  or el_to like '%art.blog%'

  or el_to like '%business.blog%'

  or el_to like '%car.blog%'

  or el_to like '%code.blog%'

  or el_to like '%data.blog%'

  or el_to like '%design.blog%'

  or el_to like '%family.blog%'

  or el_to like '%fashion.blog%'

  or el_to like '%finance.blog%'

  or el_to like '%fitness.blog%'

  or el_to like '%food.blog%'

  or el_to like '%game.blog%'

  or el_to like '%health.blog%'

  or el_to like '%home.blog%'

  or el_to like '%law.blog%'

  or el_to like '%movie.blog%'

  or el_to like '%music.blog%'

  or el_to like '%news.blog%'

  or el_to like '%photo.blog%'

  or el_to like '%poetry.blog%'

  or el_to like '%politics.blog%'

  or el_to like '%school.blog%'

  or el_to like '%science.blog%'

  or el_to like '%sport.blog%'

  or el_to like '%tech.blog%'

  or el_to like '%travel.blog%'

  or el_to like '%video.blog%'

  or el_to like '%water.blog%'

  or el_to like '%freepgs.com%'

  or el_to like '%khosann.com%'

  or el_to like '%kimkimdir.tv%'

  or el_to like '%neoldu.com%'

  or el_to like '%biyografistan.com%'

  or el_to like '%kimnereli.net%'

  or el_to like '%ismininanlamianalizi.com%'

)

AND (el_to NOT LIKE '%googleblog.blogspot%'

  AND el_to NOT LIKE '%googlepress.blogspot%'

  AND el_to NOT LIKE '%googleforwork.blogspot%'

  AND el_to NOT LIKE '%googledocs.blogspot%'

  AND el_to NOT LIKE '%googledrive.blogspot%'

  AND el_to NOT LIKE '%nisanyan1.blogspot%'

  AND el_to NOT LIKE '%google-opensource.blogspot%'

  AND el_to NOT LIKE '%gmailblog.blogspot%'

  AND el_to NOT LIKE '%googlesystem.blogspot%'

  AND el_to NOT LIKE '%nisanyan.blogspot%'

  AND el_to NOT LIKE '%googleonlinesecurity.blogspot%'

  AND el_to NOT LIKE '%googleresearch.blogspot%'

  AND el_to NOT LIKE '%googlemobile.blogspot%'

  AND el_to NOT LIKE '%googlevideo.blogspot%'

  AND el_to NOT LIKE '%wikimapiablog.blogspot%'

)

GROUP BY el_to

ORDER BY cnt desc;