MySQL Group By 在多列上相等的值 [英] MySQL Group By values that are equal over multiple columns

问题描述

对于此数据:

NAME    Col1  Col2  Col3
row1    0     2     4
row2    1     5     0
row3    1     1     0

所需的查询输出是:

ColValue CountInCol1 CountInCol2 CountInCol3
0        1           0           2
1        2           1           0
2        0           1           0
4        0           0           1
5        0           1           0

问题解释:

如果列共享公共值，如何将查询返回的行压缩到多列上?在此示例中，如何将 Col1、Col2 和 Col3 分组到相同的 ColValue 结果中?

The question explained:

If columns share common values, how can returned rows from a query be condensed on multiple columns? In this example, how can Col1, Col2, and Col3 be grouped in to the same ColValue result?

推荐答案

我会通过反透视数据然后重新透视它来做到这一点:

I would do this by unpivoting the data and then repivoting it:

select colvalue,
       sum(which = 'col1') as CountInCol1,
       sum(which = 'col2') as CountInCol2,
       sum(which = 'col3') as CountInCol3
from (select 'col1' as which, col1 as colvalue from data union all
      select 'col2', col2 from data union all
      select 'col3', col3 from data
     ) d
group by colvalue;

这篇关于MySQL Group By 在多列上相等的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！