Posts que tienen dos etiquetas dadas (escribir nombres de etiquetas en minúscula)

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
       JOIN Posts_With_Tag2 p2 ON p1.PostID=p2.PostID
   )
   
 SELECT 
   PostDate fecha, 
   PostId as [Post Link], 
   tags, 
   cntTag1 [total ##FirstTag##], 
   cntTag2 [total ##SecondTag##],
   cntBoth totalAmbas
 FROM Posts_With_Both

Enter Parameters

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