mysql - 如何对这样的 MySQL 表进行透视和计数？

Question

我有下表。我怎样才能旋转它并计算每个问题的答复？第二个表是我想要的结果。

Answer 1

试试这个，您应该将 @table 替换为您的表名称。

select 
    'Q1' as Question,
    sum(if(Q1='yes', num, 0)) as 'yes',
    sum(if(Q1='no', num, 0)) as 'no',
    sum(if(Q1='could be', num, 0)) as 'could be'
from (select Q1, count(Q1) as num from @table group by Q1) Q1
union
select 
    'Q2' as Question,
    sum(if(Q2='yes', num, 0)) as 'yes',
    sum(if(Q2='no', num, 0)) as 'no',
    sum(if(Q2='could be', num, 0)) as 'could be'
from (select Q2, count(Q2) as num from @table group by Q2) Q2
union
select 
    'Q3' as Question,
    sum(if(Q3='yes', num, 0)) as 'yes',
    sum(if(Q3='no', num, 0)) as 'no',
    sum(if(Q3='could be', num, 0)) as 'could be'
from (select Q3, count(Q3) as num from @table group by Q3) Q3
union
select 
    'Q4' as Question,
    sum(if(Q4='yes', num, 0)) as 'yes',
    sum(if(Q4='no', num, 0)) as 'no',
    sum(if(Q4='could be', num, 0)) as 'could be'
from (select Q4, count(Q4) as num from @table group by Q4) Q4