SQL
x
/*
select cl_from from categorylinks
where cl_type like "page"
and cl_to like "جميع_صفحات_التوضيح"
limit 10;
*/
/*
select count(distinct(pl_from)) from pagelinks
where pl_namespace = 0
and pl_from_namespace = 0
and pl_from not in (
select cl_from from categorylinks
where cl_type like "page"
and cl_to like "جميع_صفحات_التوضيح"
)
and pl_title like "%توضيح%"
*/
/*
select concat("[[:",page_title,"]]") as "page_title",pl_title as "to" from pagelinks
inner join page on page.page_id = pagelinks.pl_from
where pl_namespace = 0
and pl_from_namespace = 0
and pl_from not in (
select cl_from from categorylinks
where cl_type like "page"
and cl_to like "جميع_صفحات_التوضيح"
)
and pl_title like "%توضيح%"
limit 1000
*/
SELECT
#p.page_id,
#p.page_title,
concat("[[:",page_title,"]]") as "page_title",
GROUP_CONCAT(CONCAT("\n#", pl.pl_title) SEPARATOR '') AS pl_titles
#COUNT(pl.pl_from) AS pl_from_count
FROM pagelinks AS pl
INNER JOIN page AS p ON p.page_id = pl.pl_from
WHERE pl.pl_namespace = 0
AND pl.pl_from_namespace = 0
AND pl.pl_from IN (
SELECT pl2.pl_from
FROM pagelinks AS pl2
WHERE pl2.pl_namespace = 0
AND pl2.pl_from_namespace = 0
AND pl2.pl_title LIKE "%توضيح%"
GROUP BY pl2.pl_from
HAVING COUNT(pl2.pl_from) BETWEEN 2 and 4
)
AND p.page_id NOT IN (
SELECT cl.cl_from
FROM categorylinks AS cl
WHERE cl.cl_type LIKE "page"
AND cl.cl_to LIKE "جميع_صفحات_التوضيح"
)
AND pl.pl_title LIKE "%توضيح%"
GROUP BY p.page_id, p.page_title
order by pl_from_count desc
LIMIT 500;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.