在用户级别检索与每个标签对应的答案总数总数 [英] Retrieve count of total no of answers corresponding to each tag at user level

问题描述

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

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

通过< a href = https://data.stackexchange.com/stackoverflow/query/7521/how-unsung-am-i rel = nofollow noreferrer>此，我可以找到我的总数

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

我创建了查询的永久链接

这篇关于在用户级别检索与每个标签对应的答案总数总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！