mysql - 计算 2 个 mysql 列中的结果

Question

我需要计算在 2 个 mysql 列中找到结果的次数。

例如

|  Column A   |  Column B   |
|   white     |    black    |
|   black     |    green    |
|   orange    |    white    |
|   black     |    blue     |

我需要得到这个结果:

white -> 2 times
black -> 3 times
green -> 1 time
orange -> 1 time
blue -> 1 time

我为单列做了它并且有效:

SELECT 
    column_name
    , COUNT(column_name) as total 
FROM table_name 
WHERE (......) 
GROUP BY column_name 
HAVING column_name != '' 
ORDER BY total DESC

我不明白它如何处理 2 列

Answer 1

对于您的特定用途，我会做这样的事情:

SELECT col, COUNT(col) FROM
    (SELECT columnA as col FROM table
       UNION ALL SELECT columnB FROM table) as tmp_table
WHERE ... GROUP BY ... HAVING ... ORDER BY ...