Main pl.wiki query 1

This query is marked as a draft This query has been published by The Polish.
SQL

#RESULTSET_1
USE plwiki_p;
SELECT CONCAT("# [[Dyskusja:",p1.page_title,"]]") AS page_title
FROM page AS p1
WHERE p1.page_title NOT LIKE '%/%'
AND p1.page_namespace = 1
AND NOT EXISTS (SELECT 1
FROM page AS p2
WHERE p2.page_namespace = 0
AND p1.page_title = p2.page_title);
#RESULTSET_3
USE plwiki_p;
SELECT concat("# [[Dyskusja:",page_title,"]]") AS page_title
FROM plwiki_p.page
WHERE page_namespace = 1 AND page_len = 0;
#RESULTSET_4
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[',rd_title,']]') AS rd_title
FROM page
JOIN redirect
ON rd_FROM = page_id
AND NOT EXISTS (SELECT * FROM page AS p1 WHERE p1.page_title = rd_title AND p1.page_namespace = rd_namespace)
AND page_namespace = 0
AND rd_namespace = 0;
#RESULTSET_6
USE plwiki_p;
SELECT CONCAT('# [[Wikipedysta:',page_title,']]') AS page_title
FROM page, templatelinks
WHERE tl_from_namespace = 2
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title LIKE 'DNU'
AND page_title NOT LIKE '%Pablo000%'
GROUP BY page_title;
#RESULTSET_8
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title
FROM page
WHERE page_namespace = 0 AND page_len = 0;
#RESULTSET_9
USE plwiki_p;
SELECT CONCAT('# [[Specjalna:Linkujące/Szablon:',tl_title,']]') AS tl_title
FROM templatelinks, page
WHERE page_latest = 0
AND tl_from_namespace = 0
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title NOT LIKE 'Toc'
AND tl_title NOT LIKE 'Tc';
#RESULTSET_10
USE plwiki_p;
SELECT distinct CONCAT('# [[:Kategoria:',page_title,']]') AS page_title, rev_timestamp
FROM categorylinks
RIGHT JOIN page
ON cl_to = page_title
JOIN revision
ON rev_page = page_id
WHERE page_namespace = 14
AND rev_parent_id = 0
AND cl_to IS NULL
AND page_title NOT LIKE '?'
AND page_title NOT LIKE 'Łatki_-_Kilka_plików'
AND page_title NOT LIKE '%infobo%'
AND page_title NOT LIKE '%artykuł%'
AND page_title NOT LIKE '%Artykuł%'
AND page_title NOT LIKE '%Dyskusj%'
AND page_title NOT LIKE '%Stron%'
AND page_title NOT LIKE '%Hasł%'
AND page_title NOT LIKE '%Literatura_uzupełniająca%'
AND page_title NOT LIKE '%Plik%'
AND page_title NOT LIKE '%nieprawidłowej_przestrzeni%'
AND page_title NOT LIKE '%Moduł%'
AND page_title NOT LIKE '%Szablon%'
AND page_title NOT LIKE '%szablon%'
AND page_title NOT LIKE '%Kategori%'
AND page_title NOT LIKE '%kategori%'
AND page_title NOT LIKE '%Wikiprojekt%'
AND page_title NOT LIKE '%Podano_IUCN%'
AND page_title NOT LIKE '%Wikida%'
AND page_title NOT LIKE '%Wikipedyści%'
AND page_title NOT LIKE '%Parametr%'
AND page_title NOT LIKE '%łaściwoś%'
AND page_title NOT LIKE '%Linki%'
AND page_title NOT LIKE '%Coor%'
AND page_title NOT LIKE '%Zdjęci%'
AND page_title NOT LIKE '%autorytatywna%'
AND page_title NOT LIKE '%Wikipedii%'
AND page_title NOT LIKE '%PANDA%'
AND page_title NOT LIKE '%NPA%'
AND page_title NOT LIKE '%Ekspresowe_kasowanie%'
AND page_title NOT LIKE '%weryfikacji%'
AND page_title NOT LIKE '%Błęd%'
AND page_title NOT LIKE '%błęd%'
AND page_title NOT LIKE '%Medal%'
AND page_title NOT LIKE '%podano%'
AND page_title NOT LIKE '%Ombox%'
AND page_title NOT LIKE '%Pomniejsz%'
AND page_title NOT LIKE '%Przekierowania%'
AND page_title NOT LIKE '%brak%'
AND page_title NOT LIKE '%User%'
AND page_title NOT LIKE '%uzupełnienia%'
AND page_title NOT LIKE '%Przyszłe%'
AND page_title NOT LIKE '%Niezablokowane%'
AND page_title NOT LIKE '%Dorzecze_Tille%'
AND page_title NOT LIKE '%nieznane_parametry%'
AND page_title NOT LIKE '%Głosowani%'
AND page_title NOT LIKE '%wydarzenia_w_sporcie%'
AND page_title NOT LIKE '%wydarzenia_sportowe%'
AND page_title NOT LIKE '%Zdegenerowana_galeria%'
ORDER BY rev_timestamp;
#RESULTSET_11
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[Wikipedysta:',rd_title,']]') AS rd_title
FROM page
JOIN redirect
ON rd_FROM = page_id
AND page_namespace = 0
AND rd_namespace = 2;
#RESULTSET_12
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, pp_propname
FROM page_props
JOIN page
ON page_id = pp_page
WHERE page_namespace = 0
AND pp_propname IN ('index', 'newsectionlink', 'noeditsection', 'nonewsectionlink', 'notitleconvert')
AND page_title NOT LIKE '%według_pierwiastków%';
#RESULTSET_13
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title, GROUP_CONCAT(cl_to SEPARATOR ',') AS cl_to
FROM categorylinks
JOIN page ON cl_from=page_id
WHERE cl_to rlike "Urodzeni_w_1.*"
AND page_title NOT LIKE '%_i_%'
AND page_title NOT LIKE 'Bracia%'
AND page_title NOT LIKE 'Honor_Blackman'
AND page_title NOT LIKE 'Samuel_Shelley'
AND page_title NOT LIKE 'Joyce_Redman'
AND page_title NOT LIKE 'Zhu_Da'
AND page_title NOT LIKE 'Thomas_Morley'
AND page_title NOT LIKE 'Stefan_Pogonowski'
AND page_title NOT LIKE 'Rojda_Felat'
AND page_title NOT LIKE 'Jan_Horodeński'
AND page_title NOT LIKE 'Jason_Derek_Brown'
AND page_title NOT LIKE 'Jan_Kanty_Szwedkowski'
AND page_title NOT LIKE 'Damy_z_Llangollen'
AND page_title NOT LIKE 'Beverley_Bass'
GROUP BY cl_from
HAVING COUNT(cl_to)>1;
#RESULTSET_14
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title, GROUP_CONCAT(cl_to SEPARATOR ',') AS cl_to
FROM categorylinks
JOIN page ON cl_from=page_id
WHERE cl_to rlike "Zmarli_w_1.*"
AND page_title NOT LIKE '%_i_%'
AND page_title NOT LIKE 'Bracia%'
AND page_title NOT LIKE 'Spytko_IV_z_Melsztyna'
AND page_title NOT LIKE 'Damy_z_Llangollen'
GROUP BY cl_from
HAVING COUNT(cl_to)>1;
#RESULTSET_15
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cat_title,']]') AS cat_title, cat_pages
FROM page, category
WHERE page_title = cat_title
AND page_namespace = 14
AND page_is_redirect = 1;
#RESULTSET_16
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',page_title,']]') AS page_title
FROM page
LEFT OUTER JOIN categorylinks
on cl_FROM = page_id
WHERE cl_FROM is null
AND page_namespace = 14
AND page_title NOT LIKE 'Kategorie'
AND page_title NOT LIKE '%wstawienia%'
AND page_title NOT LIKE 'Wikipedysta:Pablo000/dyskusja';
#RESULTSET_17
USE plwiki_p;
SELECT DISTINCT CONCAT('# [[',page_title,']]') AS page_title
FROM page
JOIN imagelinks
ON page_id = il_from
WHERE (NOT EXISTS(
SELECT 1
FROM image
WHERE img_name = il_to))
AND (NOT EXISTS(
SELECT
1
FROM commonswiki_p.page
WHERE page_title = il_to
AND page_namespace = 6))
AND page_namespace = 0;
#RESULTSET_19
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cl_to,'|Kategoria:',cl_to,']]') AS cl_to, COUNT(*) AS count_cl_to
FROM page, categorylinks
WHERE page_id = cl_from
AND page_namespace = 0
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE cl_to = p1.page_title
AND p1.page_namespace = 14)
GROUP BY cl_to
ORDER BY count_cl_to DESC, cl_to ASC;
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.
SQL

Checking query status...
