Main pl.wiki query 1

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

#RESULTSET_1
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
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_4
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_6
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_7
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_8
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_9
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_10
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_11
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_12
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_13
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_14
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;
SELECT CONCAT('# [[',page_title,']]') as page_title
FROM page
LEFT JOIN categorylinks
ON cl_from = page_id
WHERE cl_from IS NULL
AND page_namespace = 0
AND page_is_redirect = 0;
SELECT CONCAT('# [[',p1.page_title,']]') AS page_title
FROM page p1
LEFT OUTER JOIN categorylinks
ON cl_from = p1.page_id
LEFT JOIN page p2
ON cl_to = p2.page_title AND p2.page_namespace = 14
LEFT JOIN page_props
ON p2.page_id = pp_page AND pp_propname = 'hiddencat'
WHERE p1.page_namespace = 0
AND p1.page_is_redirect = 0
AND p1.page_title NOT LIKE '%/%'
AND p1.page_title NOT LIKE 'Lista_planetoid%'
GROUP BY p1.page_title
HAVING COUNT(cl_from)-COUNT(pp_propname) = 0;
#RESULTSET_15
SELECT CONCAT('# [https://pl.wikipedia.org/w/index.php?title=Specjalna%3ALinkujące&hidelinks=1&target=Szablon:',tl_title,'&namespace=0 ',tl_title,']') AS tl_title, COUNT(*) AS count_tl_title
FROM page, templatelinks
WHERE 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'
AND tl_title NOT LIKE 'Francuska_okupacja_Malty'
AND tl_title NOT LIKE 'Mapa_dane_Polska'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE p1.page_namespace = tl_namespace
AND p1.page_title = tl_title)
GROUP BY tl_title
ORDER BY count_tl_title DESC, tl_title ASC;
#RESULTSET_16
SELECT CONCAT("# [[",page_title,"]]") AS page_title, CONCAT("[[",pl_title,"]]") AS pl_title
FROM pagelinks, page
WHERE pl_title NOT LIKE '%(%)%'
AND pl_title NOT LIKE '%:)%'
AND pl_title NOT LIKE '%:-)%'
AND pl_title NOT LIKE '%)))%'
AND pl_title NOT LIKE '%Pre)Thing%'
AND pl_from = page_id
AND pl_from_namespace = 0
AND pl_namespace = 0
AND (pl_title LIKE '%)%)%' OR pl_title LIKE '%(%(%' OR pl_title LIKE '%(%' OR pl_title LIKE '%)%')
#GROUP BY pl_title
ORDER BY page_title ASC, pl_title ASC;
#RESULTSET_17
SELECT CONCAT('# [[User talk:',page_title,']]') AS page_title
FROM page
LEFT JOIN `user` ON user_name = REPLACE(page_title, '_', ' ')
WHERE page_namespace = 2
AND page_is_redirect = 0
AND page_title NOT LIKE '%/%'
AND page_title 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]?)'
AND user_name IS NULL
ORDER BY page_len DESC;
#RESULTSET_18
SELECT tl_namespace, CONCAT('{{s|',tl_title,'}}') as tl_title, COUNT(*) AS count_tl_title, CONCAT('[https://pl.wikipedia.org/w/index.php?title=Specjalna%3ALinkujące&hidelinks=1&target=Szablon:',tl_title,'&namespace=0 Linkujące]') AS tl_title_url
FROM page, templatelinks
WHERE 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'
AND tl_title NOT LIKE 'Lista_planetoid%'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE p1.page_namespace = tl_namespace
AND p1.page_title = tl_title)
GROUP BY tl_title
ORDER BY count_tl_title DESC, tl_title 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...
