计算堆栈交换数据库中具有相同AnswerCount值的单个匹配记录的数量

Question

我正在查询位于https://data.stackexchange.com/stackoverflow/query/new

以计数具有与另一帖子相同的CCD_ 1值的帖子的总数。

这是我的尝试，导致错误Something unexpected went wrong while running your query. Don't worry, blame is already being assigned.

这个想法是在Posts表中的一个post/记录的AnswerCount值与另一个post/记录有一个匹配后增加计数器，而不计算其自身AnswerCount值的匹配。

Select Count(*)
From Posts as p1
Join Posts as p2
On p2.Id = {
Select Top 1 Id
From p2
Where p1.AnswerCount = p2.AnswerCount
And p1.Id <> p2.Id
};

这是Stack Exchange的帖子，我用它作为参考：如何加入第一行

Answer 1

计算与另一个帖子具有相同AnswerCountvalue的帖子总数

你的逻辑太复杂了。与当前行具有相同答案计数的其他行的数量与此答案计数减去1的行的数量完全相同。只有当计数等于1时，才没有其他答案，因此：

select sum(cnt)
from
( -- count per AnswerCount
Select AnswerCount, Count(*) as cnt
From Posts
group by AnswerCount
having count(*) > 1 -- remove unique AnswerCount
) as dt

或者添加更多详细信息：

select sum(cnt) as answers
,sum(case when cnt > 1 then cnt end) as same_AnswerCount_as others
,sum(case when cnt = 1 then cnt end) as Unique_AnswerCount
,max(AnswerCount)
from
( 
Select AnswerCount, Count(*) as cnt
From Posts
group by AnswerCount
) as dt

顺便说一句，即使是最简单的查询，Data Explorer当前也会出现此错误消息。

Answer 2

您的逻辑是正确的，但我会在这里使用exists:

SELECT COUNT(*)
FROM Posts p1
WHERE EXISTS (SELECT 1 FROM Posts p2
WHERE p2.Id <> p1.Id AND p2.AnswerCount = p1.AnswerCount);

用通俗易懂的英语阅读，上面的查询说要计算每个帖子记录，我们可以用不同的Id值来查找具有相同AnswerCount值的不同帖子记录(意味着它是不同的记录(。