----查询每个单位的该专业人员数量
select unitid,majorclass, count(id) cc from person
where unitid like'340%' group by unitid,majorclass
unitid majorclass cc[单位编号，专业，人数]
340201 2 97
340202 2 4
----行转列
select unitid '单位编号',unit.unitname '单位名称'
,sum(case majorclass when '1' then cc else 0 end) '通信'
,sum(case majorclass when '2' then cc else 0 end) '信号'
,sum(case majorclass when '71' then cc else 0 end) '电力'
,sum(case majorclass when '64' then cc else 0 end) '测量'
,sum(case majorclass when '65' then cc else 0 end) '桥梁'
,sum(cc) '合计'
from
(
select unitid,majorclass, count(id) cc from person where unitid like'340%' group by unitid,majorclass
)
b,unit where b.unitID=unit.unitcode GROUP by unitid,unitname
单位编号 单位名称 通信 信号 电力 测量 桥梁 合计
340201 二局一公司 0 97 0 0 0 97
340202 二局二公司 0 4 0 0 0 4