aaa

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Step 1: Find posts with only 2 tags and assign it into a temp table #A
SELECT
  PostId
INTO #A
FROM 
  PostTags
GROUP BY
  PostId
HAVING
  Count(TagId) >= 2

-- Step 2: Find the Tag IDs of such posts and assign it into a temp table #B
SELECT
  PostTags.*,
  Tags.TagName
INTO #B
FROM 
 PostTags inner join #A on #A.PostId = PostTags.PostId inner join Tags on PostTags.TagId = Tags.Id


-- Step 3: conduct a full outer join on #B where first tagid is smaller than second (to ensure unique pairs)
SELECT TOP 100
  '<' + t1.TagName + '> <' + t2.TagName + '>' as "Tag Pairs", 
--  t1.TagName +   as "Tag1Name", 
--  t2.TagName as "Tag2Name",
  COUNT(t1.PostId) as "Number of Posts"

FROM 
  #B t1 inner join #B t2 
  on t1.PostId = t2.PostId

WHERE 
  t1.TagId < t2.TagId

GROUP BY
  t1.TagName, t2.TagName

ORDER BY 
  COUNT(t1.PostId) DESC;

Enter Parameters

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