This query is marked as a draft This query has been published by Tomigaoka.

SQL

AخA
 
SELECT
    P.page_title AS "リダイレクト元カテゴリ",
    R.rd_title AS "リダイレクト先カテゴリ",
    A_FIRST.actor_name AS "リダイレクト元の初版作成者",
    H_FIRST.rev_timestamp AS "リダイレクト元の初版作成日時",
    C_FIRST.comment_text AS "リダイレクト元の初版コメント",
    A_LATEST.actor_name AS "リダイレクト元の最新版作成者",
    H_LATEST.rev_timestamp AS "リダイレクト元の最新版作成日時",
    C_LATEST.comment_text AS "リダイレクト元の最新版コメント",
    H2_LATEST.rev_count AS "リダイレクト元の総版数",
    H_LATEST.rev_timestamp - H_FIRST.rev_timestamp AS "初版と最新版までの間隔(秒)"
FROM page P
INNER JOIN redirect R
    ON P.page_id = R.rd_from 
INNER JOIN revision H_FIRST
    ON P.page_id = H_FIRST.rev_page
INNER JOIN (
  SELECT MIN(rev_id) AS rev_id, rev_page, COUNT(rev_id) AS rev_count
  FROM revision
  GROUP BY rev_page ) AS H2_FIRST
    ON H_FIRST.rev_id = H2_FIRST.rev_id AND H_FIRST.rev_page = H2_FIRST.rev_page
INNER JOIN actor A_FIRST
    ON H_FIRST.rev_actor = A_FIRST.actor_id
INNER JOIN comment C_FIRST
    ON H_FIRST.rev_comment_id = C_FIRST.comment_id
INNER JOIN revision H_LATEST
    ON P.page_id = H_LATEST.rev_page
INNER JOIN (
  SELECT MAX(rev_id) AS rev_id, rev_page, COUNT(rev_id) AS rev_count
  FROM revision
  GROUP BY rev_page ) AS H2_LATEST
    ON H_LATEST.rev_id = H2_LATEST.rev_id AND H_LATEST.rev_page = H2_LATEST.rev_page
INNER JOIN actor A_LATEST
    ON H_LATEST.rev_actor = A_LATEST.actor_id
INNER JOIN comment C_LATEST
    ON H_LATEST.rev_comment_id = C_LATEST.comment_id
WHERE P.page_namespace=14
AND R.rd_namespace=14
AND A_FIRST.actor_id = A_LATEST.actor_id
AND H2_LATEST.rev_count = 2
ORDER BY H_LATEST.rev_timestamp - H_FIRST.rev_timestamp
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.

Checking query status...