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.