MySQL行列转置

原始表格为“比赛报名信息”，需要根据行业（"高新技术产业类", "现代服务业类", "文化产业类", "综合类"）和组别（"团队组", "企业组"）产生统计结果

select cep.industry "行业", ce.type "组别", count(*) "数量"
  from c4_contest_enroll ce
  inner join c4_contest_enroll_project cep on ce.enroll_id=cep.enroll_id
  where status='audit_pass'
  group by cep.industry, ce.type;

查询结果如下：

QQ截图20170522103539.png

真实的需求是按照行业，团队，企业和总数的格式统计，需要“行列转置”，语句如下，主要使用SUM和IF实现：

select industry as "行业", sum(if(type='team', total, 0)) as "团队", sum(if(type='enterprise', total, 0)) as "企业", sum(total) "总数"
from (
  select cep.industry, ce.type, count(*) total
    from c4_contest_enroll ce
    inner join c4_contest_enroll_project cep on ce.enroll_id=cep.enroll_id
    where status='audit_pass'
    group by cep.industry, ce.type
) t
group by industry;

QQ截图20170522103817.png

首页 » MySQL行列转置

相关内容