2

Please login or register to vote for this query.

(click on this box to dismiss)

Perguntas e respostas para programadores profissionais e entusiastas

-- How many upvotes do I have for each tag? 
-- how long before I get tag badges? 
DECLARE @UserId INT = ##userid## 

SELECT --TOP 20  
tagname, 
Count(*)                AS 'Total Votos', 
Sum(CASE votetypeid 
      WHEN 2 THEN 1 
      ELSE 0 
    END)                AS UpVotes, 
Sum(CASE votetypeid 
      WHEN 3 THEN 1 
      ELSE 0 
    END)                AS DownVotes, 
( Sum(CASE votetypeid 
        WHEN 2 THEN 1 
        ELSE 0 
      END) - Sum(CASE votetypeid 
                   WHEN 3 THEN 1 
                   ELSE 0 
                 END) ) AS Pontos 
FROM   tags 
       INNER JOIN posttags 
               ON posttags.tagid = tags.id 
       INNER JOIN posts 
               ON posts.parentid = posttags.postid 
       INNER JOIN votes 
               ON votes.postid = posts.id 
                  AND votetypeid IN ( 2, 3 ) 
WHERE  posts.owneruserid = @UserId 
GROUP  BY tagname 
ORDER  BY pontos DESC

fork query

created mar 18 16 Randrade