SET NOCOUNT ON DECLARE @VoteStats TABLE (PostId int, up ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow Meta en español

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

SET NOCOUNT ON

DECLARE @VoteStats TABLE (PostId int, up int, down int)

INSERT @VoteStats
SELECT
    v.PostId,
    up = SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END),
    down = SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END)
FROM Votes v
WHERE v.PostId BETWEEN 30000000 AND 40000000  AND v.VoteTypeId IN (2, 3)
GROUP BY v.PostId

SET NOCOUNT OFF

SELECT p.id, p.body, p.title, p.creationdate, p.tags, vs.up,
       vs.down, u.Id as UserId, u.Location, u.DisplayName, u.aboutme
FROM @VoteStats vs
INNER JOIN Posts p ON vs.PostId = p.Id
INNER JOIN Users u ON p.OwnerUserId = u.Id
WHERE vs.up > 24 AND vs.down < 1 AND p.CommunityOwnedDate IS NULL 
      AND p.ClosedDate IS NULL AND LEN(u.Location) > 1
      AND (
            u.Location LIKE '%United States%' OR
            u.Location LIKE '%India%' OR
            u.Location LIKE '%United Kingdom%' OR
            u.Location LIKE '%Germany%' OR
            u.Location LIKE '%Canada%' OR
            u.Location LIKE '%France%' OR
            u.Location LIKE '%Brazil%' OR
            u.Location LIKE '%Russia%' OR
            u.Location LIKE '%Australia%' OR
            u.Location LIKE '%Italy%'
          )
      AND p.body NOT LIKE '%<code>%'
ORDER BY vs.up DESC

Enter Parameters

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