mysql - 计算多列不同值的出现次数

Question

我正在尝试找到一个 MySQL 查询，它将在特定字段中找到不同的值，计算该值的出现次数，然后对结果进行排序。

我知道如何用一行(只有 name1)来做到这一点:

SELECT name1, COUNT( * ) AS count
FROM table
GROUP BY name1
ORDER BY `count` ASC

这是否可能用于 2 行或更多行，或者我是否需要多次执行前面的查询？

例子:

    id         name1        name2
    -----      ------       ------
    1          Mark         Paul
    2          Mike         John
    3          Paul         Mike
    4          Cleo         Bert
    5          Mike         Cleo
    6          John         Mark
    7          Mark         Cleo

结果:

    names      count1       count2
    ------     -------      ------
    Bert       0            1
    Cleo       1            2
    john       1            1
    Mark       2            1
    Mike       2            1
    Paul       1            1

Answer 1

SELECT name1, COALESCE(sum(count1),0) as count1, COALESCE(sum(count2),0) as count2
FROM 
(
   select name1, count(*) as count1, null as count2 from your_table group by name1
   union all 
   select name2, null, count(*) from your_table group by name2
) tmp
GROUP BY name1
ORDER BY name1 ASC