在用户级别检索与每个标签对应的答案总数总数

问题描述：

我正在尝试创建一个查询，以查找与每个标签相对应的答案总数（在*中给出）。

I am trying to create a query, to find the count of total number of answers (that I have give in *) corresponding to each tag.

通过此，我可以找到我的总数

Through this, I am able to find total count of my accepted, scored and unscored answers.

并使用此，我能够找到每个标签有多少票。

And using this, I am able to find how many upvotes do I have of each tag.

我在 data.StackExcange> 中运行了以下查询理想的结果，但我没有成功。

I ran the below query in the data.StackExcange to get the desired result, but I did not succeed.

这是我的查询：

DECLARE @UserId int = ##UserId##

SELECT --TOP 20 
    TagName,
    count(a.Id) as [Accepted Answers]
from
    Posts q
  inner join
    Posts a
  on a.Id = q.AcceptedAnswerId

WHERE 
    Posts.OwnerUserId = @UserId
    a.CommunityOwnedDate is null
  and a.OwnerUserId = ##UserId##
  and q.OwnerUserId != ##UserId##
  and a.postTypeId = 2
GROUP BY TagName

更新1：

我还必须找到问题的网址，标题以及与该答案对应的所有其他标签。

答

以下查询查找针对用户的每个标签的答案总数。它不考虑用户的自我回答的问题，因为它几乎没有额外的计数。

Below query finds the total number of answers against each tag for a user. It does not consider the self-answered questions of the user, as it can give little additional count.

--Self answered questions dont count
select t.TagName, COUNT(q.Id) as countofAnsweredQuestions
from Posts q
inner join PostTags AS pt
ON pt.PostId = q.Id
inner join Posts a
on a.parentId = q.Id
inner join Tags as t
on pt.tagId = t.Id
where q.CommunityOwnedDate is null and q.ClosedDate is null 
  and a.OwnerUserId = ##UserId##
  and q.OwnerUserId != ##UserId##
  and a.postTypeId = 2
GROUP BY t.TagName
ORDER BY countofAnsweredQuestions desc

我创建了查询的永久链接

在用户级别检索与每个标签对应的答案总数总数

相关推荐