All posts with two tags

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

With 
  Posts_With_Tag1 AS
  ( SELECT posts.title as title, posts.tags as tags, posts.CreationDate as PostDate, p.PostId as PostId, (1.*(COUNT(*) OVER (PARTITION BY 1))) as cnt
       FROM PostTags p
       JOIN Tags t ON t.Id = p.TagId
       JOIN Posts posts ON posts.Id = p.PostId 
      WHERE t.TagName = ##FirstTag:string##
   ),
   
  Posts_With_Tag2 AS
  ( SELECT p.PostId as PostId, (1.*(COUNT(*) OVER (PARTITION BY 1))) as cnt
      FROM PostTags p
        JOIN Tags t ON t.Id = p.TagId
      WHERE t.TagName = ##SecondTag:string##
   ),
   
   Posts_With_Both AS
   ( select p1.PostDate as PostDate, p1.title as title, p1.tags as tags, p1.PostID as PostID, p1.cnt as cntTag1, p2.cnt as cntTag2, (1.*(COUNT(*) OVER (PARTITION BY 1))) as cntBoth
     from Posts_With_Tag1 p1
       Inner join Posts_With_Tag2 p2 ON p1.PostID=p2.PostID
   )
 Select PostDate, title, tags, cntTag1, cntTag2,cntBoth from Posts_With_Both

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results