1

Please login or register to vote for this query.

(click on this box to dismiss)

Вопросы и ответы о сайте для программистов

---the query looks at the first revisiont with the given tag
---mostly that could correspond to tag creator
---in some cases the post where the tag was created might have been deleted


SELECT
  Id AS phid,
  replace(value,'>','') TagName
INTO #tagshistory_cte  
FROM
  PostHistory
  CROSS APPLY STRING_SPLIT(Text,'<')
Where PostHistoryTypeId in (3,6,9)
and len(value) > 0

go

SELECT
  tagname,
  MIN(phid) AS firstid
INTO #tagsfirst_cte
FROM #tagshistory_cte
GROUP BY tagname


/*; with tagshistory_cte AS
(
SELECT
  Id AS phid,
  replace(value,'>','') TagName
FROM
  PostHistory
  CROSS APPLY STRING_SPLIT(Text,'<')
Where PostHistoryTypeId in (3,6,9)
and len(value) > 0
), tagsfirst_cte AS (
SELECT
  tagname,
  MIN(phid) AS firstid
FROM #tagshistory_cte
GROUP BY tagname
)
*/

SELECT
  t.tagname,
  ph.CreationDate,
  ph.UserId AS [User Link],
  url = 'site://posts/' + CAST(ph.PostId AS varchar)+'/revisions',
  ph.PostId AS [Post Link],
  ph.Comment
FROM #tagsfirst_cte t INNER JOIN PostHistory ph ON ph.id=t.firstid
WHERE ph.UserId=##userid##
ORDER BY ph.id DESC

fork query

edited dec 26 20

created dec 26 20 Martin20528