öksüz diye işaretlenmiş ama öksüz olmayan maddeler

Fork of Articles with "orphan" tag, that has at least one incoming link (lvwiki) by Edgars2007
This query is marked as a draft This query has been published by Bulgu.
SQL

USE trwiki_p;
#SELECT p.page_title, COUNT(pl.pl_from) FROM page p
SELECT CONCAT("https://tr.wikipedia.org/w/index.php?action=edit&section=0&title=",replace(p.page_title," ","_")) as link
,	COUNT(pl.pl_from) as kaclink
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Öksüz_maddeler"
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
INNER JOIN page p2 ON p2.page_id = pl.pl_from
AND pl.pl_from_namespace = 0 and p2.page_is_redirect=0# and p.page_title="Milica_Daboviča"
GROUP BY p.page_title
having COUNT(pl.pl_from)>0
ORDER BY COUNT(pl.pl_from) DESC
limit 100;
/* 
USE lvwiki_p;
SELECT disambigs.page_title, COUNT(DISTINCT links.pl_from) AS direct_links, COUNT(DISTINCT redirects.page_id) AS redirects,
	COUNT(DISTINCT links.pl_from) + COUNT(DISTINCT links_to_redirects.pl_from) AS all_backlinks
	FROM (SELECT * FROM categorylinks where cl_to = "Izolētie_raksti") AS disambigs
	JOIN page AS disambigs
		ON disambigs.page_id = cl_from
	JOIN (SELECT * FROM pagelinks WHERE pl_from_namespace = 0) AS links
		ON links.pl_title = disambigs.page_title AND links.pl_namespace = disambigs.page_namespace
	LEFT JOIN page AS redirects
		ON redirects.page_is_redirect = 1 AND redirects.page_id = links.pl_from AND redirects.page_namespace = disambigs.page_namespace
	LEFT JOIN (SELECT * FROM pagelinks WHERE pl_from_namespace = 0) AS links_to_redirects
		ON links_to_redirects.pl_title = redirects.page_title AND links_to_redirects.pl_namespace = redirects.page_namespace
GROUP BY disambigs.page_title
ORDER BY all_backlinks DESC, direct_links DESC, redirects, disambigs.page_title
LIMIT 100;



arī uz pāradresācijām
use lvwiki_p;
SELECT pl.pl_namespace, pl.pl_title, count(pl.pl_from), pl.pl_from_namespace#p.page_title, COUNT(pl.pl_from)
from pagelinks pl
where pl.pl_namespace=0 

and pl.pl_from_namespace=0
and #pl.pl_from_namespace=0 and  - svarīgi arī veidnēs izvākt
pl.pl_title in (SELECT p2.page_title
from pagelinks pl2
join page p2 on pl2.pl_from=p2.page_id
where p2.page_is_redirect=1 and pl2.pl_namespace=0
		and pl2.pl_from_namespace=0
      and #pl2.pl_from_namespace=0 and  - svarīgi arī veidnēs izvākt
      pl2.pl_title in (SELECT p3.page_title
					FROM page p3
					INNER JOIN categorylinks c3 ON p3.page_id = c3.cl_from AND c3.cl_to = "Izolētie_raksti"
					INNER JOIN pagelinks pl3 ON p3.page_title = pl3.pl_title AND p3.page_namespace = pl3.pl_namespace
					INNER JOIN page p23 ON p23.page_id = pl3.pl_from
					AND pl3.pl_from_namespace = 0)
                                                                                                         
                                                                                                         )
group by pl.pl_title
having count(pl.pl_from)>0
order by count(pl.pl_from)


USE lvwiki_p;
#SELECT p.page_title, COUNT(pl.pl_from) FROM page p
SELECT 
	CONCAT('* [[Special:Whatlinkshere/',p.page_title,']]') as "Saites uz rakstu",
	CONCAT(' - [[',p.page_title,']] - ') as "Raksts",
	COUNT(pl.pl_from)
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti"
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
INNER JOIN page p2 ON p2.page_id = pl.pl_from
AND pl.pl_from_namespace = 0 and p2.page_is_redirect=0# and p.page_title="Milica_Daboviča"
GROUP BY p.page_title
having COUNT(pl.pl_from)>0
ORDER BY COUNT(pl.pl_from) DESC
limit 100;



USE lvwiki_p;
SELECT p.page_title, COUNT(pl.pl_from) FROM page p, pagelinks plw
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti"
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
AND pl.pl_from_namespace = 0 and plw.page_is_redirect=0
GROUP BY p.page_title
ORDER BY COUNT(pl.pl_from) DESC

use lvwiki_p;
SELECT
  page_namespace,
  page_title
FROM page
LEFT JOIN pagelinks
ON pl_title = page_title
AND pl_namespace = page_namespace
WHERE pl_namespace=0 and page_is_redirect=0
LIMIT 1;
*/
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...
