Benutzer Diskussion:Mathias Schindler/Interwikiliste

Die Abfrage

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)