跨列计算不同的值

ldioqlga 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(344)

我有这样的数据：

-----------------------------------------------------
|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

来源：https://stackoverflow.com/questions/50891967/counting-distinct-values-across-columns

2条答案

按热度按时间

8nuwlpux1#

使用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;

请参阅SQLFiidle演示

赞(0）回复(0）举报 2021-06-20

2eafrhcq2#

你可以用 union 但是我想你可能需要一个更好的数据结构

select col,count(*) as qty
from(
    select col1 as col from table
    union all
    select col2 as col from table
    union all
    select col3 as col from table
    union all
    select col4 as col from table
    union all
    select col5 as col from table
    union all
    select col6 as col from table
) t
group by col

赞(0）回复(0）举报 2021-06-20

我来回答

跨列计算不同的值

2条答案

相关问题

热门标签

最新问答