使用mysql COUNT计数多列 [英] Using mysql COUNT to count multiple columns

问题描述

我在像这样的表中有信息：

I have information in a table like so:

id    name    recommender
 1    daniel  steve
 2    daniel  tony
 3    steve   daniel

，此代码为：

<h2>Follow/Join Recommendation League Table</h2>
<br/>
<?php

$query = "SELECT name_of_follower, COUNT(name) FROM recommendation_competition_entrants GROUP BY name_of_follower ORDER BY COUNT(name) DESC"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
        echo $row['name_of_follower'] . " has ". $row['COUNT(name)'] . " entries.";
        echo "<br />";
}

?>

这会计算名称列中的所有条目，并显示一个迷你联赛表，如下所示：

This counts all entries in the 'name' column and displays a mini league table which looks like this:

daniel - 2 entries
steve - 1 entry

我现在要做的是从名称&推荐者列，因此表格如下所示：

What I need to do now is count names from both the name & recommender columns so the table would look like this:

daniel - 3 entries
steve - 2 entries
tony - 1 entry

有一种简单的方法吗？

感谢您的帮助

推荐答案

select name_of_follower, 
       count(name_of_follower) 
  from ( select name as name_of_follower 
          from abc 
         union all 
        select follower as name_of_follower  
          from abc
       ) t
 group by t.name_of_follower 
 order by count(name_of_follower) desc

这篇关于使用mysql COUNT计数多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！