select substring(a.unionid,1,4) as
unionid, b.grade_name,count(*) as amount,
sum(case b.sex when 1 then 0 else 1 end) as girl,
sum(case b.sex when 2 then 0 else 1 end) as boy
from cnet2_sys_unit a,student_archive
b where a.unitid = b.school_id and a.isdeleted = '0' and a.unionid like '35%'
group by substring(a.unionid,1,4),b.grade_name
上面这段sql语句查询unionid的前4位，grade_name，总人数，男女人数。其中统计男女人数是用了case和sum语法，很好的解决了统计性别困难的问题，这样就不用另外写语句统计男女生人数了