mysql - 如何列出同一个表的多个列的值并统计这些值在mysql中每列重复了多少次？

Question

我有这些数据表

+---------+---------+---------+---------+
| format1 | format2 | format3 | format4 |
+---------+---------+---------+---------+
|       1 |       1 |       3 |       4 |
|       1 |       2 |       3 |       7 |
|       1 |       1 |       3 |       7 |
|       1 |       2 |       2 |       3 |
|       2 |       1 |       7 |       1 |
|       2 |       2 |       7 |      34 |
|     ... |     ... |     ... |     ... |
+---------+---------+---------+---------+

我需要一个代表下一个方案的查询:将整个数据表的唯一(不同)值放在第一列中，并计算每种格式重复它们的次数，如下所示:

+---------+-----------+-----------+-----------+-----------+
|  format | f1  count | f2  count | f3  count | f4  count |
+---------+-----------+-----------+-----------+-----------+
|       1 |         4 |         3 |      null |         1 |
|       2 |         2 |         3 |         1 |      null |
|       3 |      null |      null |         3 |         1 |
|       4 |      null |      null |      null |         1 |
|       7 |      null |      null |         2 |         2 |
|      34 |      null |      null |      null |         1 |
|     ... |       ... |       ... |       ... |       ... |
+---------+-----------+-----------+-----------+-----------+

第一列表示插入到通过此查询检索的所有表列中的所有数字的不同列表(按 ASC 排序)

SELECT distinct format1 AS val
FROM table
UNION
SELECT distinct format2 AS val
FROM table
UNION
SELECT distinct format3 AS val
FROM table
UNION
SELECT distinct format4 AS val
FROM table
ORDER BY  val ASC 

每列(首先排除)是相应格式中数字出现的次数。

我尝试合并许多 SELECT 查询但没有成功(这取决于有多少种格式)。

如何将它们全部组合起来？

Answer 1

解决方案很简单...重新构建问题...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,format_type INT NOT NULL
,value INT NOT NULL
);

INSERT INTO my_table (format_type,value) VALUES
(1,     1), 
(1,     1), 
(1,     1), 
(1,     1), 
(1,     2), 
(1,     2), 
(2,     1), 
(2,     2), 
(2,     1), 
(2,     2), 
(2,     1), 
(2,     2), 
(3,     3), 
(3,     3), 
(3,     3), 
(3,     2), 
(3,     7), 
(3,     7), 
(4,     4), 
(4,     7), 
(4,     7), 
(4,     3), 
(4,     1), 
(4,    34);

SELECT * FROM my_table;
+----+-------------+-------+
| id | format_type | value |
+----+-------------+-------+
|  1 |           1 |     1 |
|  2 |           1 |     1 |
|  3 |           1 |     1 |
|  4 |           1 |     1 |
|  5 |           1 |     2 |
|  6 |           1 |     2 |
|  7 |           2 |     1 |
|  8 |           2 |     2 |
|  9 |           2 |     1 |
| 10 |           2 |     2 |
| 11 |           2 |     1 |
| 12 |           2 |     2 |
| 13 |           3 |     3 |
| 14 |           3 |     3 |
| 15 |           3 |     3 |
| 16 |           3 |     2 |
| 17 |           3 |     7 |
| 18 |           3 |     7 |
| 19 |           4 |     4 |
| 20 |           4 |     7 |
| 21 |           4 |     7 |
| 22 |           4 |     3 |
| 23 |           4 |     1 |
| 24 |           4 |    34 |
+----+-------------+-------+

SELECT format_type,value,COUNT(*) FROM my_table GROUP BY format_type,value;
+-------------+-------+----------+
| format_type | value | COUNT(*) |
+-------------+-------+----------+
|           1 |     1 |        4 |
|           1 |     2 |        2 |
|           2 |     1 |        3 |
|           2 |     2 |        3 |
|           3 |     2 |        1 |
|           3 |     3 |        3 |
|           3 |     7 |        2 |
|           4 |     1 |        1 |
|           4 |     3 |        1 |
|           4 |     4 |        1 |
|           4 |     7 |        2 |
|           4 |    34 |        1 |
+-------------+-------+----------+

问题的其余部分 - 处理缺失值和格式设置最好在表示层/应用程序级代码中处理(假设您有)。