我都要被这个问题搞疯了
我现在有很多表
emp表显示员工信息 需要用emp_id(number),emp_dept(number 外键到dept_id),emp_joindate(date型),emp_enterdate(date),educateon(varchar2(20))
dr表显示部门调动 需要dr_date(date),dr_dept_f(varchar2(20)调动前部门),dr_dept_a(varchar2(20)调动后部门)
el表 显示员工离职 需要el_date(date),el_dept(number 外键dept_id)
dept表显示部门信息 需要dept_id(number),dept_name(varchar2(20))现在我要查询某月的 部门名称(dept_name) 部门人数 员工入职数(count(emp_joindate)) 员工离职数(count(el_date)) 员工调入数(count(dr_dept_a)) 员工调出数(count(dr_dept_f)) 并统计员工的学历(education)的人数(研究生,本科,大专,高中级以下)用一个Sql语句写出 包含以上内容 并且只得出列如下
部门名称,部门人数 员工入职数,员工离职数,员工调入数,员工调出数,研究生,本科,大专,高中级以下求大神解答
我现在有很多表
emp表显示员工信息 需要用emp_id(number),emp_dept(number 外键到dept_id),emp_joindate(date型),emp_enterdate(date),educateon(varchar2(20))
dr表显示部门调动 需要dr_date(date),dr_dept_f(varchar2(20)调动前部门),dr_dept_a(varchar2(20)调动后部门)
el表 显示员工离职 需要el_date(date),el_dept(number 外键dept_id)
dept表显示部门信息 需要dept_id(number),dept_name(varchar2(20))现在我要查询某月的 部门名称(dept_name) 部门人数 员工入职数(count(emp_joindate)) 员工离职数(count(el_date)) 员工调入数(count(dr_dept_a)) 员工调出数(count(dr_dept_f)) 并统计员工的学历(education)的人数(研究生,本科,大专,高中级以下)用一个Sql语句写出 包含以上内容 并且只得出列如下
部门名称,部门人数 员工入职数,员工离职数,员工调入数,员工调出数,研究生,本科,大专,高中级以下求大神解答
count(el.el_date), count(drt.dr_dept_a), count(dr.dr_dept_f),
emp.education, count(emp.education)
from dept, emp, dr, el, dr drt
where
dept.dept_id = emp.emp_dept
and dept.dept_id = el.el_dept
and dept.dept_id = dr.dr_dept_f
and dept.dept_id = drt.dr_dept_a
and emp.emp_joindate between '2011-10-01' and '2011-10-31'
and el.el_date between '2011-10-01' and '2011-10-31'
and dr.dr_date between '2011-10-01' and '2011-10-31'
and drt.dr_date between '2011-10-01' and '2011-10-31'
group by dept_name,emp.education
楼上这个语句运行完毕什么结果都没有了 请至少留个dept_name,0,0,0,0,0,0,0,0,0这种的呗
谢谢楼上
2:我觉得不复杂 用 case WHEN ELSE END 语句 行列转换
而这个做出的东西很急 所以请给我个代码吧 谢谢
需要注意的是查询条件的那部分。
改称下面的试下select dept.dept_name , count(emp.emp_joindate),
count(el.el_date), count(drt.dr_dept_a), count(dr.dr_dept_f),
emp.education, count(emp.education)
from dept, emp, dr, el, dr drt
where
dept.dept_id(+) = emp.emp_dept
and dept.dept_id(+) = el.el_dept
and dept.dept_id(+) = dr.dr_dept_f
and dept.dept_id(+) = drt.dr_dept_a
and emp.emp_joindate between To_date('2011-10-01', 'yyyy-mm-dd') and To_date('2011-10-31', 'yyyy-mm-dd')
and el.el_date between To_date('2011-10-01', 'yyyy-mm-dd') and To_date('2011-10-31', 'yyyy-mm-dd')
and dr.dr_date between To_date('2011-10-01', 'yyyy-mm-dd') and To_date('2011-10-31', 'yyyy-mm-dd')
and drt.dr_date between To_date('2011-10-01', 'yyyy-mm-dd') and To_date('2011-10-31', 'yyyy-mm-dd')
group by dept_name,emp.education
dept.dept_id = emp.emp_dept(+)
select A.dept_name, B.C, C.C
from dept A
left join (select emp_dept,count(1) C from emp group emp_dept) B
on a.dept_id = b.emp_dept
left join (select el_dept,count(1) C from el group by el_dept) C
on a.dept_id = c.el_dept
left join (select dr_dept_a,count(1) from dr group by dr_dept_a) D
on a.dept_id = d.dr_dept_a
left join (select dr_dept_f,count(1) from dr group by dr_dept_f) E
on a.dept_id = e.dr_dept_f
也可以用分析函数来写,不过我觉得还是用外联接的比较容易理解。
现在的sql语句是
select DISTINCT dept.dept_name , count(emp.emp_joindate), COUNT(emp.emp_enterdate),
count(el.el_date), count(drt.dr_dept_a), count(dr.dr_dept_f)
from dept
LEFT OUTER JOIN emp
ON dept.dept_id = emp.emp_dept
AND to_char(emp.emp_joindate,'yyyy-mm') <='2011-10'
AND to_char(emp.emp_enterdate,'yyyy-mm') <='2011-10'
LEFT OUTER JOIN el
ON dept.dept_id =el.el_dept
and To_char(el.el_date, 'yyyy-mm') = '2011-10'
LEFT OUTER JOIN dr
ON dept.dept_name = dr.dr_dept_f
and To_char(dr.dr_date, 'yyyy-mm') = '2011-10'
LEFT OUTER JOIN dr drt
ON dept.dept_name = drt.dr_dept_a
and To_char(drt.dr_date, 'yyyy-mm') = '2011-10'
group by dept_name
但是得到的数据都是一样的而且数量非常多 表里12条数据 查到的count值是49
from dept A
left join (select emp_dept,count(1) C from emp where to_char(emp_enterdate,'yyyy-mm') <='2011-10') and to_char(emp_joindate,'yyyy-mm') <='2011-10') group emp_dept) B
on a.dept_id = b.emp_dept
left join (select el_dept,count(1) C from el where To_char(el_date, 'yyyy-mm') = '2011-10' group by el_dept) C
on a.dept_id = c.el_dept
left join (select dr_dept_a,count(1) from dr where To_char(dr_date, 'yyyy-mm') = '2011-10' group by dr_dept_a) D
on a.dept_id = d.dr_dept_a
left join (select dr_dept_f,count(1) from dr where To_char(drt.dr_date, 'yyyy-mm') = '2011-10' group by dr_dept_f) E
on a.dept_id = e.dr_dept_f