itWP: Schweizer

Artikel in den Kategorien Svizzera und Svizzeri und deren Unterkategorien, ausgenommen die Artikel welche nur in den Kategorien Lingue_della_Svizzera und Federazioni_sportive_per_disciplina (und deren Unterkategorien) sind.

Download Data … Wikitable … copy/paste nach Wikipedia … Vorschau oder Wartungsliste anlegen

SQL

WITH RECURSIVE Cat AS (SELECT page_title, page_title AS path
                        FROM page
                       WHERE (page_title = 'Svizzera' OR page_title = 'Svizzeri') /* Subcats of these two Categories */
                         AND page_namespace = 14
                       UNION
                      SELECT SubCat.page_title, CONCAT(path, ' -> ', SubCat.page_title)
                        FROM page AS SubCat, categorylinks, Cat
                       WHERE SubCat.page_namespace = 14
                         AND cl_from = SubCat.page_id
                         AND cl_to = Cat.page_title
                         AND cl_type = "subcat"
                         AND SubCat.page_title NOT IN ("Lingue_della_Svizzera", "Federazioni_sportive_per_disciplina") /*Exclude Categories*/
                     )
SELECT CONCAT('[[', REPLACE(Article.page_title, '_', ' '), ']]') AS "Artikel",
       GROUP_CONCAT(CONCAT('[[Category:', REPLACE(Cat.page_title, '_', ' '), ']] via ', path) SEPARATOR ", ") AS "Kategorie"
  FROM Cat, categorylinks, page as Article
 WHERE cl_to = Cat.page_title
   AND Article.page_id = cl_from
   AND Article.page_namespace = 0
   AND cl_type = "page"
 GROUP BY Article.page_title
 ORDER BY Article.page_title;

SQL

Checking query status...