Retrieve all questions that are marked as duplicated, with additional information.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

SELECT TOP 100
  questions.Id AS QuestionId,
  questions.Title AS QuestionTitle,
  questions.Body AS QuestionBody,
  questions.Score AS QuestionScore,
  questions.AcceptedAnswerId AS AcceptedAnswer,
  accepted.Score AS AcceptedAnswerScore,
  answers.Id AS MostVotedAnswer,
  answers.Score AS MostVotedAnswerScore,
  -- History text example: {"OriginalQuestionIds":[1234],"Voters":[{"Id":5678,"DisplayName":"Foo Bar"}]}
  SUBSTRING(history.Text, CHARINDEX('[', history.Text) + 1, CHARINDEX(']', history.Text) - CHARINDEX('[', history.Text) - 1) AS DuplicateQuestions,
  LEN(history.Text) - LEN(REPLACE(history.Text, '"Id"', '###')) AS DuplicateQuestionsVotes
FROM
  Posts questions
  LEFT JOIN Posts accepted ON accepted.Id = questions.AcceptedAnswerId
  LEFT JOIN Posts answers ON answers.ParentId = questions.Id
  LEFT JOIN PostHistory history ON history.PostId = questions.Id
WHERE (
  ( -- It must me marked as duplicated
    history.Id IN (
      SELECT TOP 1
        h.Id
      FROM PostHistory h
      WHERE (
        ( -- Is history of the correct question
          h.PostId = questions.Id
        ) AND ( -- Is Closed
          h.PostHistoryTypeId = 10
        ) AND ( -- Is duplicated
          h.Text LIKE '{"OriginalQuestionIds":%'
        )
      )
      ORDER BY h.Id DESC
    )
  ) AND ( -- The answer retrieved must be the most voted answer
    (
      answers.Id IS NULL
    ) OR (
      answers.Id IN (
        SELECT TOP 1
          p.Id
        FROM Posts p
        WHERE ( -- Is answer of the correct question
          p.ParentId = questions.Id
        )
        ORDER BY p.Score DESC, p.Id DESC
      )
    )
  )
)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results