Benutzer Diskussion:Mathias Schindler/Interwikiliste
Zur Navigation springen
Zur Suche springen
Die Abfrage
[Quelltext bearbeiten]Die SQL-Abfrage, die zur Erstellung der Listen verwendet werden kann (allerdings nicht in Spezial:Asksql).
drop table if exists no_iw1; create table no_iw1( cur_id int(8) unsigned not null, primary key (cur_id) ); insert into no_iw1 select cur_id from cur where cur_namespace = 0 and cur_is_redirect = 0 --hier ist die einzige Stelle, an der der Sprachcode --der gewünschten nichtverlinkten Wikipedia steht and cur_text not like '%[[de:%' and cur_text regexp '\\[\\[[^]]{1,5}:'; drop table if exists no_iw2; create table no_iw2( cur_id int(8) unsigned not null, count_interwiki smallint(4) unsigned not null, primary key (cur_id), key c_i (count_interwiki) ); insert into no_iw2 select cur.cur_id, --hier steht (hoffentlich) für jede Wikipedia --eine Zeile mit dem richtigen Sprachcode if(cur_text like '%[[w:%',1,0)+ if(cur_text like '%[[m:%',1,0)+ if(cur_text like '%[[meta:%',1,0)+ if(cur_text like '%[[sep11:%',1,0)+ if(cur_text like '%[[simple:%',1,0)+ if(cur_text like '%[[aa:%',1,0)+ if(cur_text like '%[[ab:%',1,0)+ if(cur_text like '%[[af:%',1,0)+ if(cur_text like '%[[als:%',1,0)+ if(cur_text like '%[[am:%',1,0)+ if(cur_text like '%[[an:%',1,0)+ if(cur_text like '%[[ang:%',1,0)+ if(cur_text like '%[[ar:%',1,0)+ if(cur_text like '%[[as:%',1,0)+ if(cur_text like '%[[ay:%',1,0)+ if(cur_text like '%[[az:%',1,0)+ if(cur_text like '%[[ba:%',1,0)+ if(cur_text like '%[[be:%',1,0)+ if(cur_text like '%[[bg:%',1,0)+ if(cur_text like '%[[bh:%',1,0)+ if(cur_text like '%[[bi:%',1,0)+ if(cur_text like '%[[bn:%',1,0)+ if(cur_text like '%[[bo:%',1,0)+ if(cur_text like '%[[br:%',1,0)+ if(cur_text like '%[[bs:%',1,0)+ if(cur_text like '%[[ca:%',1,0)+ if(cur_text like '%[[chr:%',1,0)+ if(cur_text like '%[[co:%',1,0)+ if(cur_text like '%[[cs:%',1,0)+ if(cur_text like '%[[csb:%',1,0)+ if(cur_text like '%[[cy:%',1,0)+ if(cur_text like '%[[da:%',1,0)+ if(cur_text like '%[[de:%',1,0)+ if(cur_text like '%[[dk:%',1,0)+ if(cur_text like '%[[dz:%',1,0)+ if(cur_text like '%[[el:%',1,0)+ if(cur_text like '%[[en:%',1,0)+ if(cur_text like '%[[eo:%',1,0)+ if(cur_text like '%[[es:%',1,0)+ if(cur_text like '%[[et:%',1,0)+ if(cur_text like '%[[eu:%',1,0)+ if(cur_text like '%[[fa:%',1,0)+ if(cur_text like '%[[fi:%',1,0)+ if(cur_text like '%[[fj:%',1,0)+ if(cur_text like '%[[fo:%',1,0)+ if(cur_text like '%[[fr:%',1,0)+ if(cur_text like '%[[fy:%',1,0)+ if(cur_text like '%[[ga:%',1,0)+ if(cur_text like '%[[gd:%',1,0)+ if(cur_text like '%[[gl:%',1,0)+ if(cur_text like '%[[gn:%',1,0)+ if(cur_text like '%[[gu:%',1,0)+ if(cur_text like '%[[gv:%',1,0)+ if(cur_text like '%[[ha:%',1,0)+ if(cur_text like '%[[he:%',1,0)+ if(cur_text like '%[[hi:%',1,0)+ if(cur_text like '%[[hr:%',1,0)+ if(cur_text like '%[[hu:%',1,0)+ if(cur_text like '%[[hy:%',1,0)+ if(cur_text like '%[[ia:%',1,0)+ if(cur_text like '%[[id:%',1,0)+ if(cur_text like '%[[ie:%',1,0)+ if(cur_text like '%[[ik:%',1,0)+ if(cur_text like '%[[io:%',1,0)+ if(cur_text like '%[[is:%',1,0)+ if(cur_text like '%[[it:%',1,0)+ if(cur_text like '%[[iu:%',1,0)+ if(cur_text like '%[[ja:%',1,0)+ if(cur_text like '%[[jbo:%',1,0)+ if(cur_text like '%[[jv:%',1,0)+ if(cur_text like '%[[ka:%',1,0)+ if(cur_text like '%[[kk:%',1,0)+ if(cur_text like '%[[kl:%',1,0)+ if(cur_text like '%[[km:%',1,0)+ if(cur_text like '%[[kn:%',1,0)+ if(cur_text like '%[[ko:%',1,0)+ if(cur_text like '%[[ks:%',1,0)+ if(cur_text like '%[[ku:%',1,0)+ if(cur_text like '%[[kw:%',1,0)+ if(cur_text like '%[[ky:%',1,0)+ if(cur_text like '%[[la:%',1,0)+ if(cur_text like '%[[lb:%',1,0)+ if(cur_text like '%[[lo:%',1,0)+ if(cur_text like '%[[lt:%',1,0)+ if(cur_text like '%[[lv:%',1,0)+ if(cur_text like '%[[mg:%',1,0)+ if(cur_text like '%[[mi:%',1,0)+ if(cur_text like '%[[minnan:%',1,0)+ if(cur_text like '%[[mk:%',1,0)+ if(cur_text like '%[[ml:%',1,0)+ if(cur_text like '%[[mn:%',1,0)+ if(cur_text like '%[[mo:%',1,0)+ if(cur_text like '%[[mr:%',1,0)+ if(cur_text like '%[[ms:%',1,0)+ if(cur_text like '%[[my:%',1,0)+ if(cur_text like '%[[na:%',1,0)+ if(cur_text like '%[[nah:%',1,0)+ if(cur_text like '%[[nb:%',1,0)+ if(cur_text like '%[[nds:%',1,0)+ if(cur_text like '%[[ne:%',1,0)+ if(cur_text like '%[[nl:%',1,0)+ if(cur_text like '%[[nn:%',1,0)+ if(cur_text like '%[[no:%',1,0)+ if(cur_text like '%[[oc:%',1,0)+ if(cur_text like '%[[om:%',1,0)+ if(cur_text like '%[[or:%',1,0)+ if(cur_text like '%[[pa:%',1,0)+ if(cur_text like '%[[pl:%',1,0)+ if(cur_text like '%[[ps:%',1,0)+ if(cur_text like '%[[pt:%',1,0)+ if(cur_text like '%[[qu:%',1,0)+ if(cur_text like '%[[rm:%',1,0)+ if(cur_text like '%[[rn:%',1,0)+ if(cur_text like '%[[ro:%',1,0)+ if(cur_text like '%[[roa-rup:%',1,0)+ if(cur_text like '%[[ru:%',1,0)+ if(cur_text like '%[[rw:%',1,0)+ if(cur_text like '%[[sa:%',1,0)+ if(cur_text like '%[[sc:%',1,0)+ if(cur_text like '%[[sd:%',1,0)+ if(cur_text like '%[[sg:%',1,0)+ if(cur_text like '%[[sh:%',1,0)+ if(cur_text like '%[[si:%',1,0)+ if(cur_text like '%[[sk:%',1,0)+ if(cur_text like '%[[sl:%',1,0)+ if(cur_text like '%[[sm:%',1,0)+ if(cur_text like '%[[sn:%',1,0)+ if(cur_text like '%[[so:%',1,0)+ if(cur_text like '%[[sq:%',1,0)+ if(cur_text like '%[[sr:%',1,0)+ if(cur_text like '%[[ss:%',1,0)+ if(cur_text like '%[[st:%',1,0)+ if(cur_text like '%[[su:%',1,0)+ if(cur_text like '%[[sv:%',1,0)+ if(cur_text like '%[[sw:%',1,0)+ if(cur_text like '%[[ta:%',1,0)+ if(cur_text like '%[[te:%',1,0)+ if(cur_text like '%[[tg:%',1,0)+ if(cur_text like '%[[th:%',1,0)+ if(cur_text like '%[[ti:%',1,0)+ if(cur_text like '%[[tk:%',1,0)+ if(cur_text like '%[[tl:%',1,0)+ --if(cur_text like '%[[tlh:%',1,0)+ if(cur_text like '%[[tn:%',1,0)+ if(cur_text like '%[[to:%',1,0)+ if(cur_text like '%[[tokipona:%',1,0)+ if(cur_text like '%[[tp:%',1,0)+ if(cur_text like '%[[tpi:%',1,0)+ if(cur_text like '%[[tr:%',1,0)+ if(cur_text like '%[[ts:%',1,0)+ if(cur_text like '%[[tt:%',1,0)+ if(cur_text like '%[[tw:%',1,0)+ if(cur_text like '%[[ug:%',1,0)+ if(cur_text like '%[[uk:%',1,0)+ if(cur_text like '%[[ur:%',1,0)+ if(cur_text like '%[[uz:%',1,0)+ if(cur_text like '%[[vi:%',1,0)+ if(cur_text like '%[[vo:%',1,0)+ if(cur_text like '%[[wa:%',1,0)+ if(cur_text like '%[[wo:%',1,0)+ if(cur_text like '%[[xh:%',1,0)+ if(cur_text like '%[[yi:%',1,0)+ if(cur_text like '%[[yo:%',1,0)+ if(cur_text like '%[[za:%',1,0)+ if(cur_text like '%[[zh:%',1,0)+ if(cur_text like '%[[zh-ch:%',1,0)+ if(cur_text like '%[[zh-tw:%',1,0)+ if(cur_text like '%[[zh-min-nan:%',1,0)+ if(cur_text like '%[[zh-cfr:%',1,0)+ if(cur_text like '%[[zu:%',1,0) as count_interwiki from cur, no_iw1 as n where cur.cur_id = n.cur_id; select concat( --hier ist die einzige Stelle, an der der Sprachcode der Wikipedia steht, --deren Artikel gerade durchsucht werden '*[http://fr.wikipedia.org/wiki/', replace(replace(cur_title, '\'', '%27'), '"', '%22'), ' ', cur_title, '] - ', count_interwiki) from cur, no_iw2 as n where cur.cur_id = n.cur_id and count_interwiki>1 order by count_interwiki desc, cur_title asc --hier kann die gewünschte Anzahl der ausgegebenen Artikel eingestellt werden limit 200;
--SirJective 15:12, 6. Nov 2004 (CET)