mysql在多列中按值分组

Question

    code1 | code2 | code3 | code4 | code5
row1    A       B       C
row2    B       A       D        E
row3   F       C 

如何将此表与codeX列中的值计数进行分组

A: 2
   B: 2
   C: 2
   D: 1
   E: 1
   F: 1

Answer 1

SELECT productCode, count(*) AS count
FROM
(
    SELECT Code1 AS productCode FROM table
    UNION ALL
    SELECT Code2 FROM table
     UNION ALL
    SELECT Code3 FROM table
     UNION ALL
    SELECT Code4 FROM table
     UNION ALL
    SELECT Code5 FROM table

) AS codes
GROUP BY productCode
ORDER BY count DESC