This query is marked as a draft This query has been published by Matěj Suchánek.

SQL

x
 
USE cswiki_p;
SELECT pages.page_title, pages.page_namespace, GROUP_CONCAT(tl_title ORDER BY tl_title ASC SEPARATOR ", "), COUNT(*)
    FROM templatelinks
    JOIN (SELECT * FROM page WHERE page_is_redirect = 0) AS templates
        ON templates.page_title = tl_title AND templates.page_namespace = tl_namespace
    JOIN page AS pages
        ON pages.page_id = tl_from
WHERE pages.page_namespace = 0
AND tl_title LIKE "Infobox&_%" ESCAPE "&"
AND tl_title NOT LIKE "%/%"
AND tl_title NOT LIKE "%&_položka%" ESCAPE "&"
AND tl_title NOT LIKE "%&_medaile%" ESCAPE "&"
AND tl_title NOT LIKE "%podinfobox%"
AND tl_title NOT IN ("Infobox_hlavička", "Infobox_konec", "Infobox_obrázek", "Infobox_začátek",
                     "Infobox_-_chronologie", "Infobox_-_sakrální_budova")
GROUP BY tl_from HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, pages.page_title, pages.page_namespace;
SELECT templates, ct, COUNT(*) FROM (
    SELECT tl_from AS id, GROUP_CONCAT(tl_title ORDER BY tl_title ASC SEPARATOR ", ") AS templates, COUNT(*) AS ct
    FROM templatelinks
    WHERE tl_from_namespace = 0
    AND tl_title LIKE "Infobox&_%" ESCAPE "&"
    AND tl_title NOT LIKE "%/%"
    AND tl_title NOT LIKE "%&_položka%" ESCAPE "&"
    AND tl_title NOT LIKE "%&_medaile%" ESCAPE "&"
    AND tl_title NOT LIKE "%podinfobox%"
    AND tl_title NOT IN ("Infobox_hlavička", "Infobox_konec", "Infobox_obrázek", "Infobox_začátek")
    GROUP BY tl_from HAVING COUNT(*) > 1
) AS sq
JOIN page ON page_id = id
GROUP BY templates
ORDER BY COUNT(*) DESC, ct DESC, templates;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...