MySQL:两个计数之和(通过distinct值)

Question

我的MySQL-table保存引用。每行是一个引用，如下所示：

A= citer，B= cited (即A引用B)。

我知道如何获得(1)谁引用A最多的数字，以及(2) A引用最多的是谁：

/* (1) who cited A most often? */
SELECT citer,COUNT(citer) AS citations1 FROM `table` WHERE cited='A' GROUP BY citer ORDER BY citations1 DESC

/* (2) whom did A cite most often? */
SELECT cited,COUNT(cited) AS citations2 FROM `table` WHERE citer='A' GROUP BY cited ORDER BY citations2 DESC

现在我想要的是得到这两个统计量的总和(citations1 + citations2)，这样我就可以知道谁与A的总引用链接最多。

例如:如果B引用A五(5)次，A引用B三(3)次，那么A-B链接的总和是八(8)。

这可以用MySQL公式实现吗？谢谢你的帮助!

Answer 1

您可以将其编写为：

select person, (sum(citers) + sum(citeds)) as total
from ((select citer as person, count(*) as citers, 0 as citeds
       from citations
       where cited = 'A'
       group by citer
      ) union all
      (select cited, 0, count(*) as citeds
       from citations
       where citer = 'A'
       group by cited
      )
     ) c
group by person
order by total desc;

这个问题有点棘手。如果您尝试使用join，您将排除具有最多引用链接的人只是一个“柠檬者”或只是一个“被引用”的可能性。

Answer 2

您可以对结果执行join操作：

select t1.citer as person, t1.citations1 + t2.citations2 as result
from
(
SELECT citer,COUNT(citer) AS citations1 FROM `table` WHERE cited='A' GROUP BY citer ORDER BY citations1 DESC
) t
join
(
SELECT cited,COUNT(cited) AS citations2 FROM `table` WHERE citer='A' GROUP BY cited ORDER BY citations2 DESC
) t2
on t1.citer = t2.cited

Answer 3

我在子查询中使用UNION，然后是行的总和

SELECT other, SUM(citations)
FROM (
   SELECT citer other,COUNT(*) AS citations 
   FROM citations 
   WHERE cited='A'        
   GROUP BY citer 
   UNION
   SELECT cited, COUNT(*)
   FROM citations 
   WHERE citer='A' 
   GROUP BY cited) AS uni
GROUP BY other