Wikipedia:Redaktion Physik/Kategorienbaum/Doppelkategorisierung

Seiten aus dem Physikkategorienbaum, die in einer Kategorie und gleichzeitig in deren Oberkategorie einsortiert wurden:

Kein Eintrag

Query

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;