Wikipedia:Redaktion Physik/Kategorienbaum/Doppelkategorisierung
Zur Navigation springen
Zur Suche springen
Seiten aus dem Physikkategorienbaum, die in einer Kategorie und gleichzeitig in deren Oberkategorie einsortiert wurden:
Kein Eintrag
Query
[Quelltext bearbeiten]DROP PROCEDURE IF EXISTS createcattree; DELIMITER // CREATE PROCEDURE createcattree() BEGIN SET @qryInsTemp = 'INSERT IGNORE INTO cattemp (ctt, ctid, pct) SELECT page_title, page_id, ct FROM cat INNER JOIN dewiki_p.categorylinks ON ct=cl_to INNER JOIN dewiki_p.page ON cl_from = page_id WHERE done = 0 AND page_namespace=14'; PREPARE stmtInsTemp FROM @qryInsTemp; REPEAT DELETE FROM cattemp; EXECUTE stmtInsTemp; UPDATE cat SET done=1 WHERE done=0; INSERT IGNORE INTO cat (ct, pct, done) SELECT ctt, pct, 0 FROM cattemp; UNTIL ROW_COUNT() = 0 END REPEAT; END // DELIMITER ; CREATE TEMPORARY TABLE IF NOT EXISTS cat (ct VARCHAR(255) PRIMARY KEY, pct VARCHAR(255), done INT, INDEX(done)); CREATE TEMPORARY TABLE IF NOT EXISTS cattemp (ctt VARCHAR(255), ctid INT, pct VARCHAR(255)); INSERT IGNORE INTO cat (ct, pct, done) VALUES('Physik', null, 0), ('Vorlage:Physik', null, 0), ('Datei:Physik', null, 0); CALL createcattree(); SELECT DISTINCT CONCAT('# [[:', IF(ns_id=0,'',CONCAT(ns_name,':')), REPLACE(p1.page_title,'_',' '),']](→[[:Kategorie:', REPLACE(p2.page_title,'_',' '),'|K:',REPLACE(p2.page_title,'_',' '),']])→[[:Kategorie:', REPLACE(p3.page_title,'_',' '),'|K:',REPLACE(p3.page_title,'_',' '),']]') FROM cat INNER JOIN dewiki_p.categorylinks cl0 ON ct=cl0.cl_to INNER JOIN dewiki_p.page p1 ON cl0.cl_from = p1.page_id INNER JOIN dewiki_p.categorylinks cl11 ON p1.page_id=cl11.cl_from # page categories INNER JOIN dewiki_p.page p2 ON cl11.cl_to=p2.page_title INNER JOIN dewiki_p.categorylinks cl2 ON p2.page_id=cl2.cl_from INNER JOIN dewiki_p.categorylinks cl12 ON p1.page_id=cl12.cl_from and cl12.cl_to = cl2.cl_to INNER JOIN dewiki_p.page p3 ON cl2.cl_to=p3.page_title INNER JOIN toolserver.namespacename ON p1.page_namespace=ns_id LEFT JOIN dewiki_p.templatelinks tl1 ON p1.page_id=tl1.tl_from AND tl1.tl_namespace=10 AND tl1.tl_title='Doppelkategorisierung' LEFT JOIN dewiki_p.templatelinks tl2 ON p2.page_id=tl2.tl_from AND tl2.tl_namespace=10 AND tl2.tl_title='Doppelkategorisierung' LEFT JOIN dewiki_p.templatelinks tl3 ON p3.page_id=tl3.tl_from AND tl3.tl_namespace=10 AND tl3.tl_title='Doppelkategorisierung' WHERE p2.page_namespace=14 AND p3.page_namespace=14 AND p1.page_id != p2.page_id AND p2.page_id != p3.page_id AND cl2.cl_to !='Versteckte_Kategorie' AND tl1.tl_from IS NULL AND tl2.tl_from IS NULL AND tl3.tl_from IS NULL AND dbname='dewiki_p' AND ns_type='primary' ORDER BY p1.page_namespace, p1.page_title, p2.page_title, p3.page_title;