SQL语句面试题

SQL语句面试题

select a.user_job as "职业",b.man_num as "男性人数",c.woman_num as "女性人数",count(id) as "总人数" from user a left join (select user_job,count(id) as man_num from user where user_sex=1 group by user_job) b on a.user_job=b.user_job left join (select user_job,count(id) as woman_num from user where user_sex=0 group by user_job) c on a.user_job=c.user_job group by a.user_job
这样写试一下，看看是否好用。

select user_job as 职业,sum(case when user_sex=0 then 1 else 0 end) as 女性人数,
sum(case when user_sex=1 then 1 else 0 end) as 男性人数，count（*）as 总人数 from user
group by user_job；