0

Please login or register to vote for this query.

(click on this box to dismiss)

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 < 10000000  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

fork query

edited mar 2

created mar 2 anonymous