时间戳

首页 > 解决方案 > 计算跨列的不同值

mysql - 计算跨列的不同值

问题描述

我有这样的数据：

-----------------------------------------------------
|id |  col1 |  col2 |  col3 |  col4 |  col5 |  col6 |
-----------------------------------------------------
| 1 |   12  |    0  |   10  |   12  |    0  |   11  |
| 2 |   12  |    0  |    0  |   10  |    0  |   11  |
| 3 |   12  |   14  |    0  |    0  |    0  |   11  |
| 4 |   12  |    0  |    0  |   11  |   14  |   11  |
| 5 |   12  |    0  |   14  |   10  |    0  |   11  |
| 6 |   12  |   10  |    0  |    0  |    0  |   11  |
-----------------------------------------------------

我需要返回 6 列中所有不同值的计数，如下所示：

----------------
| Qty | myValue|
----------------
|  4  |   10   |
|  7  |   11   |
|  7  |   12   |
|  3  |   14   |
----------------

这可能在一个查询中吗？我可以在这样的一列上执行此操作：

SELECT count(*) AS Qty, col1 AS myValue FROM myTable GROUP BY col1

但不确定如何在该语句中包含其他 5 列

标签： mysql

解决方案

使用 UNION ALL 您可以分别获取每一列并在最后按计数分组

SELECT myValue, COUNT(*) as total
FROM 
  (
    (SELECT col1 as myValue FROM a)
    UNION ALL
    (SELECT col2 as myValue FROM a)
    UNION ALL
    (SELECT col3 as myValue FROM a)
    UNION ALL
    (SELECT col4 as myValue FROM a)
    UNION ALL
    (SELECT col5 as myValue FROM a)
    UNION ALL
    (SELECT col6 as myValue FROM a)
  ) T
GROUP BY myValue
HAVING myValue > 0;

参见SQL 提琴演示

推荐阅读