Mysql多列组合

Question

我的表格是这种格式

item  A  B  C  D
i1    4  0  2  0
i2    0  2  1  0
i3    2  0  0  2
i4    3  0  1  1

而且，我正在寻找两列组合在一起的输出，如果两个元素的值都>0，则输出值被视为1。总计数是根据所有记录计算的

w1 w2   out
A  B    0
A  C    2
A  D    2
B  C    1
B  D    0
C  D    1

i,e for columns (A,C)>0 只有i1和i4满足.So out=2

到目前为止，我已经通过查询每个项目然后在 php 中对值求和来解决这个问题。这完全可以通过 sql 查询实现吗？

Answer 1

您可以在 SQL 中完成，但我认为您仍然必须考虑所有不同的组合。这是使用 union all 和条件聚合的一种方法:

select col1name, col2name,
       sum(col1 > 0 and col2 > 0)
from (select 'A' as col1name, 'B' as col2name, A as col1, B as col2
      from t
      union all
      select 'A', 'C', A, C
      from t
      union all
      select 'A', 'D', A, D
      from t
      union all
      select 'B', 'C', B, C
      from t
      union all
      select 'B', 'D', B, D
      from t
      union all
      select 'C', 'D', C, D
      from t
     ) t

编辑:

还有另一种方法，如果您对数据进行逆透视。这从这个查询开始:

select item, n.colname,
       (case when n.colname = 'A' then A
             when n.colname = 'B' then B
             when n.colname = 'C' then C
             else D
        end) as colval
from t cross join
     (select ';A' as colname union all select 'B' union all select 'C' union all select 'D'
     );

我们现在可以对查询进行自连接以获取所有组合并聚合以获取结果:

select col1.colname, col2.colname,
       sum(col1.colval > 0 and col2.colval > 0)
from (select item, n.colname,
             (case when n.colname = 'A' then A
                   when n.colname = 'B' then B
                   when n.colname = 'C' then C
                   else D
              end) as colval
      from t cross join
           (select ';A' as colname union all select 'B' union all select 'C' union all select 'D'
           )
     ) col1 join
     (select item, n.colname,
             (case when n.colname = 'A' then A
                   when n.colname = 'B' then B
                   when n.colname = 'C' then C
                   else D
              end) as colval
      from t cross join
           (select ';A' as colname union all select 'B' union all select 'C' union all select 'D'
           )
     ) col2
     on col1.item = col2.item and
        col1.colname < col2.colname
group by col1.colname, col2.colname;

如果你有超过四列，这个版本会更简单。第一种方法中的组合数量很快就会变得繁琐。