创建电子游戏专题条目最多的用户 (zhwiki)

SQL

USE zhwiki_p;

WITH pages_with_editing AS (
  SELECT
    rev_page,
    rev_actor
  FROM
    revision,
    (
      SELECT
        MIN(rev_id) as rev_id_min
      FROM
        revision
      GROUP BY
        rev_page
    ) AS r
  WHERE
    revision.rev_id = r.rev_id_min
),
pages_vg_talk AS (
  SELECT
    page.page_namespace,
    page.page_title
  FROM
    page
    JOIN categorylinks ON cl_to = '电子游戏条目'
    AND cl_type = 'page'
    AND cl_from = page.page_id
  WHERE
    page.page_namespace = 1
),
pages_vg AS (
  SELECT
    page.page_id
  FROM
    page
    JOIN pages_vg_talk ON page.page_title = pages_vg_talk.page_title
    AND page.page_namespace + 1 = pages_vg_talk.page_namespace
),
actor_with_editing AS (
  SELECT
    pages_with_editing.rev_actor,
    COUNT(*) AS cot
  FROM
    pages_with_editing,
    pages_vg
  WHERE
    pages_with_editing.rev_page = pages_vg.page_id
  GROUP BY
    pages_with_editing.rev_actor
)
SELECT
  CONCAT(
    '* {{User|',
    actor.actor_name,
    '}} <small>[',
    actor_with_editing.cot,
    ']</small>'
  ) AS result
FROM
  actor_with_editing
  JOIN actor ON actor_with_editing.rev_actor = actor.actor_id
ORDER BY
  actor_with_editing.cot DESC,
  actor.actor_name ASC;
SQL

Checking query status...
