mysql - 加入三个计数查询并将结果放入三列

Question

我想组合三个计数查询并将结果放入单独的列中。这就是我的数据集的样子。

我试过这个

select id, sum(val = 3) as valcount3
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub1
group by id
union all
select id, sum(val = 2) as valcount2
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub
group by id
union all
select id, sum(val = 1) as valcount1
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub
group by id;

这是我的输出

但预期的输出是这样的

标签： mysqlsqljoincount

Answer 1

只需使用条件聚合。. . 多次：

select id, sum(val = 3) as valcount3,
       sum(val = 2) as valcount2,
       sum(val = 1) as valcount1
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub1
group by id;