我要查询emp表下emp_education列值的个数
education有 研究生,本科,大专,高中及以下,null 5种值
我现在要查询 同一部门emp_dept下 研究生,本科,大专,高中及以下各有多少人
这个sql语句怎么写列最好是emp_dept,研究生,本科,大专,高中及以下 这5列
education有 研究生,本科,大专,高中及以下,null 5种值
我现在要查询 同一部门emp_dept下 研究生,本科,大专,高中及以下各有多少人
这个sql语句怎么写列最好是emp_dept,研究生,本科,大专,高中及以下 这5列
SELECT EMP_DEPT,
SUM(DECODE(EMP_EDUCATION,'研究生',C,0)) "研究生",
SUM(DECODE(EMP_EDUCATION,'本科'C,0)) "本科",
SUM(DECODE(EMP_EDUCATION,'大专',C,0)) "大专",
SUM(DECODE(EMP_EDUCATION,'高中',C,0)) "高中",
SUM(DECODE(EMP_EDUCATION,'以下',C,0)) "以下",
FROM
(SELECT EMP_DEPT,EMP_EDUCATION,COUNT(1) C
FROM EMP_EDUCATION
GROUP BY EMP_DEPT,EMP_EDUCATION)
select emp_dept,
sum(decode(emp_education,'研究生',1,0)) "研究生",
sum(decode(emp_education,'本科',1,0)) "本科",
sum(decode(emp_education,'大专,1,0)) "大专",
sum(decode(emp_education,'高中及以下',1,0)) "高中及以下",
sum(nvl2(emp_education,0,1)) "无信息"
from emp group by emp_dept order by 1
sum(decode(emp_education,'研究生',1,0)) "研究生",
sum(decode(emp_education,'本科',1,0)) "本科",
sum(decode(emp_education,'大专',1,0)) "大专",
sum(decode(emp_education,'高中及以下',1,0)) "高中及以下",
sum(nvl2(emp_education,0,1)) "无信息"
from emp group by emp_dept order by 1
sum(case when name='研究生' then 1 else 0 end),
sum(case when name='本科' then 1 else 0 end),
sum(case when name='大专' then 1 else 0 end),
sum(case when name='高中及以下' then 1 else 0 end)
from tab --(多表链接的话,自己加条件)
group by emp_dept
sum(decode(emp_education,'研究生',1,0)) "研究生",
sum(decode(emp_education,'本科',1,0)) "本科",
sum(decode(emp_education,'大专',1,0)) "大专",
sum(decode(emp_education,'高中及以下',1,0)) "高中及以下",
sum(nvl2(emp_education,0,1)) "无信息"
from emp
group by emp_dept