查找与用户级别的特定标记对应的每个答案中有多少次

Question

继续我以前的问题

我可以使用这个查询在用户级别获得与特定标签对应的答案的帖子链接、标题和计数。

​

​

现在，我正在试图找到答案的上行数，对应于用户级别的特定标记.。

我在data.stackexchange中运行了下面的查询，以获得所需的结果，但没有成功。

这是查询

DECLARE @UserID int = ##UserID:int##;
SELECT DISTINCT T.TagName,
       COUNT(A.ID) OVER (PARTITION BY T.Id) AS AnswersInTag,
       Q.Title,
       COUNT(*) AS UpVotes,
       CONCAT('https://stackoverflow.com/questions/',Q.ID,'/') AS URL
FROM dbo.Posts Q
     JOIN dbo.Posts A ON Q.Id = A.ParentID
     JOIN PostTags PT ON Q.Id = PT.PostId
     JOIN Tags T ON T.Id = PT.TagId
     JOIN Votes ON Votes.PostId = A.Id and VoteTypeId = 2
WHERE A.OwnerUserID = @UserID AND
T.TagName='elasticsearch-query';

有人能帮我解决这个问题吗？

Answer 1

另一种猜测，但也许..。

DECLARE @UserID int = ##UserID:int##;
SELECT DISTINCT T.TagName,
       COUNT(A.ID) OVER (PARTITION BY T.Id) AS AnswersInTag,
       Q.Title,
       SUM(V.Upvotes) OVER (PARTITION BY T.Id) AS UpVotesInTag,
       CONCAT('https://stackoverflow.com/questions/',Q.ID,'/') AS URL
FROM dbo.Posts Q
     JOIN dbo.Posts A ON Q.Id = A.ParentID
     JOIN PostTags PT ON Q.Id = PT.PostId
     JOIN Tags T ON T.Id = PT.TagId
     CROSS APPLY (SELECT COUNT(*) AS UpVotes
                  FROM dbo.Votes V
                  WHERE V.PostId = A.Id
                    AND V.VoteTypeId = 2) V
WHERE A.OwnerUserID = @UserID
ORDER BY UpVotesInTag DESC;

塞德

如果您要查询Stack Exchange数据库，我建议您花时间学习它的模型。问题似乎源于你对它缺乏了解，然而，如果你花时间学习它，这是一个相当简单的设计。