关于SQL面试题

题目如下：

员工表tl_employee

(

com_emp_id int(6) notnull pk,

name varchar(30) notnull,

age number not null,

sex char(1)not null

….

);

假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人：

sex =’男’ and age> 35

sex =’男’ and age< 35

sex =’女’ and age> 35

sex =’女’ and age< 35

每种员工的数量

解决方案1：

select count(t1.com_emp_id) as 大于35男士人数,
       count(t2.com_emp_id) as 小于35男士人数,
       count(t3.com_emp_id) as 大于35女士人数,
       count(t4.com_emp_id) as 小于35女士人数
  from tl_employee t
  left join tl_employee t1 on t1.com_emp_id = t.com_emp_id and t1.sex = '男' and t1.age > 35
  left join tl_employee t2 on t2.com_emp_id = t.com_emp_id and t2.sex = '男' and t2.age < 35
  left join tl_employee t3 on t3.com_emp_id = t.com_emp_id and t3.sex = '女' and t3.age > 35
  left join tl_employee t4 on t4.com_emp_id = t.com_emp_id and t4.sex = '女' and t4.age < 35

解决方案2：

select sum(case when sex = '男' and age > 35 then 1 else 0 end) as 大于35男士人数,
       sum(case when sex = '男' and age < 35 then 1 else 0 end) as 小于35男士人数,
       sum(case when sex = '女' and age > 35 then 1 else 0 end) as 大于35女士人数,
       sum(case when sex = '女' and age < 35 then 1 else 0 end) as 小于35女士人数
  from tl_employee

如果更好的方案请大家补充，谢谢!