Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Jura1
.
Toggle Highlighting
SQL
# test # See also https://upload.wikimedia.org/wikipedia/commons/f/f7/MediaWiki_1.24.1_database_schema.svg SELECT SQL_NO_CACHE wp.pp_value, GROUP_CONCAT(wp.wiki) As wikis, MIN(wp.cl_timestamp), GROUP_CONCAT(DISTINCT wp.year) As YD FROM ( (SELECT pp_value, cl_timestamp, "en" As Wiki, SUBSTRING(cl_to, 1, 4) As Year FROM enwiki_p.categorylinks JOIN enwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^[12][0-9]{3}_deaths" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "de" As Wiki, SUBSTRING(cl_to, 11 , 4) As Year FROM dewiki_p.categorylinks JOIN dewiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Gestorben_[12][0-9]{3}" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "pl" As Wiki, SUBSTRING(cl_to, 10, 4) As Year FROM plwiki_p.categorylinks JOIN plwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Zmarli_w_[12][0-9]{3}" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "es" As Wiki, SUBSTRING(cl_to, 15, 4) As Year FROM eswiki_p.categorylinks JOIN eswiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Fallecidos_en_[12][0-9]{3}" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "it" As Wiki, SUBSTRING(cl_to, 11, 4) As Year FROM itwiki_p.categorylinks JOIN itwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Morti_nel_[12][0-9]{3}" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "fi" As Wiki, SUBSTRING(cl_to, 8, 4) As Year FROM fiwiki_p.categorylinks JOIN fiwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Vuonna_[12][0-9]{3}_kuolleet" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "tr" As Wiki, SUBSTRING(cl_to, 1, 4) As Year FROM trwiki_p.categorylinks JOIN trwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^[12][0-9]{3}_yılında_ölenler" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "sv" As Wiki, SUBSTRING(cl_to, 9, 4) As Year FROM svwiki_p.categorylinks JOIN svwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Avlidna_[12][0-9]{3}" AND pp_propname="wikibase_item" AND cl_type="page" ) UNION ALL (SELECT pp_value, cl_timestamp, "pt" As Wiki, SUBSTRING(cl_to, 11, 4) As Year FROM ptwiki_p.categorylinks JOIN ptwiki_p.page_props ON pp_page=cl_from WHERE cl_to RLIKE "^Mortos_em_[12][0-9]{3}" AND pp_propname="wikibase_item" AND cl_type="page" ) ) As wp WHERE wp.pp_value NOT IN (SELECT page_title FROM wikidatawiki_p.page JOIN wikidatawiki_p.pagelinks ON page_id=pl_from WHERE pl_title = "P570" AND pl_namespace = 120) GROUP BY wp.pp_value
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...