Sous-pages utilisateur non liées à un utilisateur sur WP:FR

SQL

SELECT
  CONCAT("{{u'|", REPLACE(page_title, '_', ' '), "}}") AS sortie
FROM page LEFT JOIN `user` ON REPLACE(SUBSTRING_INDEX(page.page_title, '/', 1), '_', ' ') = user.user_name /* Get page name, extract substring till char '/' and replace '_' by ' '. This is user name, use it to LEFT JOIN with 'user' table. */
WHERE page_namespace IN (2,3) /* PU et PddU */
AND page_title LIKE "%/%" /* cibler les sous-pages uniquement */
AND SUBSTRING_INDEX(page_title, '/', 1) NOT RLIKE "(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)" /* éliminer IPv4 */
AND SUBSTRING_INDEX(page_title, '/', 1)  NOT RLIKE "(^([0-9]{1,3}[.]){3}[0-9]{1,3}$|^([[:alnum:]]{1,4}[:]){7})" /* éliminer IPv6 */
AND user_name IS NULL /* sans nom d'utilisateur */
GROUP BY page_title /* éliminer les doublons */
ORDER BY page_title /* trier par ordre alphabetique */
SQL

Checking query status...
