使用Stack Exchange Data Explorer(SEDE)通过帖子计数和信誉来查找用户

Question

我想找出哪些用户的声誉最高，帖子数量最少(少于10条(。但是，为什么在该联接之前不能有where子句？：

SELECT TOP 100 Users.Id, Users.DisplayName AS [Username], Users.Reputation, COUNT(Posts.Id) AS [Post Count] FROM Users
//WHERE COUNT(Posts.Id) < 10
JOIN Posts ON Posts.OwnerUserId = Users.Id 
GROUP BY Users.Id, Users.DisplayName, Users.Reputation
ORDER BY Users.Reputation DESC;

原始用户后计数示例查询位于data.stackeexchange.com/stackeoverlow/query/503051

Answer 1

这就是HAVING子句(MS引用(的作用。

您将使用：

SELECT TOP 100 Users.Id, Users.DisplayName AS [Username], Users.Reputation, COUNT(Posts.Id) AS [Post Count] FROM Users
JOIN Posts ON Posts.OwnerUserId = Users.Id
GROUP BY Users.Id, Users.DisplayName, Users.Reputation
HAVING COUNT(Posts.Id) < 10
ORDER BY Users.Reputation DESC;

但现在，它利用了一些SEDE功能：

-- maxRows: How many rows to return:
-- maxPosts: Maximum number of posts a user can have:
SELECT TOP ##maxRows:INT?100##
'site://u/' + CAST(u.Id AS NVARCHAR) + '|' + u.DisplayName  AS [User]
, u.Reputation
, COUNT (p.Id)  AS [Post Count]
FROM        Users u
LEFT JOIN   Posts p         ON (p.OwnerUserId = u.Id  AND  p.PostTypeId IN (1, 2) )  -- Q & A only
GROUP BY    u.Id
, u.DisplayName
, u.Reputation
HAVING      COUNT (p.Id) <= ##maxPosts:INT?10##
ORDER BY    u.Reputation DESC
, [Post Count]
, u.DisplayName

您可以在SEDE中看到它的直播。

我特别喜欢那些没有帖子的具有较高代表性的用户。