نقاش ويكيبيديا:تقارير قاعدة البيانات/مقالات بدون وصلات داخلية

Query

SELECT CONCAT("| {{subst:formatnum:", @curRow := @curRow + 1, "}} || [[", REPLACE(page title, " ", " "), "]]||" ,page_len,"|| [[مستخدم:", rev_user_text,"|",rev_user_text, "]]\n|-")
  FROM (SELECT page_title,page_len, l.rev_user_text
          FROM page
          JOIN (SELECT rev_page, rev_user_text
                  FROM revision
              GROUP BY rev_page) l ON l.rev_page = page_id
         WHERE page_id NOT IN (SELECT pl_from FROM pagelinks)
           AND page_namespace = 0
           AND page_is_redirect = 0
        ) p
  JOIN (SELECT @curRow := 0) r ORDER BY page_len DESC;

الاستعلام

SELECT p.page_title as ll_page_title, p.page_len as ll_page_len, a.actor_name as ll_user_name
FROM page p
         LEFT JOIN pagelinks pl ON p.page_id = pl.pl_from
         INNER JOIN revision r ON p.page_id = r.rev_page
         INNER JOIN actor a ON r.rev_actor = a.actor_id
WHERE pl.pl_from IS NULL AND p.page_is_redirect = 0 AND p.page_namespace = 0
  AND r.rev_id = (SELECT MIN(rev_id) FROM revision WHERE rev_page = p.page_id)

-- Lokas _{(راسلني)} 14:59، 22 ديسمبر 2022 (ت ع م)