遇到了不会写的sql，没有思路

Question

Answer 1

 select max(year) as '年份',
       (select count(*) from 表 b where b.sex='男' and b.company='c1' and a.year=b.year) as 'c1男' ,
       (select count(*) from 表 c where c.sex='女' and c.company='c1' and a.year=c.year) as 'c1女' ,
       (select count(*) from 表 d where d.sex='男' and d.company='c2' and a.year=d.year) as 'c2男' ,
       (select count(*) from 表 e where e.sex='女' and e.company='c2' and a.year=e.year) as 'c2女' ,
       (select count(*) from 表 f where a.year=f.year) as '合计'

from 表 a group by a.year union all
select '合计', 
       (select count(*) from 表  where sex='男' and company='c1') as 'c1男' ,
       (select count(*) from 表  where sex='女' and company='c1') as 'c1女' ,
       (select count(*) from 表  where sex='男' and company='c2') as 'c2男' ,
       (select count(*) from 表  where sex='女' and company='c2') as 'c2女' ,
       (select count(*) from 表  ) as '合计'
 from 表 group by '合计'