select emp.name,pe.pe_score
from employee emp,dept,pe
where emp.dept_id = dept.dept_id
and dept.dept_name = your_dept_name
and emp.archive_id = pe.archieves_id
and pe.year = your_year
and pe.quarter = your_quarter不太明白你的考核类别表及考核项目表是什么意思
from employee emp,dept,pe
where emp.dept_id = dept.dept_id
and dept.dept_name = your_dept_name
and emp.archive_id = pe.archieves_id
and pe.year = your_year
and pe.quarter = your_quarter不太明白你的考核类别表及考核项目表是什么意思
123456 2002 1 1 80 1
123465 2002 1 1 85 2
123456 2002 1 2 70 1
123456 2002 2 1 65 1
......
最后要得到的结果是这样的:
部门名 人名 年 考核项目名 考核类别名 考核分数
部门1 aa 2002 工作量 自评 一年4季度平均分
部门1 aa 2002 工作量 组评 一年4季度平均分
部门1 aa 2002 完成情况 自评 一年4季度平均分
部门1 aa 2002 完成情况 组评 一年4季度平均分
insert #employee values(1,'wsbll',1)
insert #employee values(2,'大力',1)create table #dept(dept_id int,dept_name varchar(100))
insert #dept values(1,'CSDN财务部')create table #pe (id int,archives_id int,year int,quarter int,pepara_id int,pe_score int,pe_class_id int)
insert #pe values(1,1,2003,1,1,100,1)
insert #pe values(2,1,2003,1,1,99,2)
insert #pe values(3,1,2003,1,2,98,1)
insert #pe values(4,1,2003,1,2,97,2)
insert #pe values(5,2,2003,1,1,87,1)
insert #pe values(6,2,2003,1,1,82,2)
insert #pe values(7,2,2003,1,2,75,1)
insert #pe values(8,2,2003,1,2,56,2)create table #pe_class(pe_class_id int,class_name varchar(100))
insert #pe_class values(1,'自评')
insert #pe_class values(1,'部门评')create table #pepara(pepara_id int,pe_name varchar(100))
insert #pepara values(1,'工作量')
insert #pepara values(2,'完成情况')
select e.dept_name 部门,a.archives_id 编号,b.name 姓名,c.class_name 考核类别,d.pe_name 考核项目,a.pe_score
from #pe a join #employee b on a.archives_id=b.archives_id
join #pe_class c on a.pe_class_id=c.pe_class_id
join #pepara d on a.pepara_id=d.pepara_id
join #dept e on b.dept_id=e.dept_id
where a.year=2003 and a.quarter=1 and e.dept_name='CSDN财务部'
order by a.archives_id,c.class_name----得到平均分:
select d.dept_name 部门,b.name 姓名,c.class_name 考核类别,a.pe_score
from (select archives_id,pe_class_id,avg(pe_score) pe_score from #pe where year=2003 and quarter=1 group by archives_id,pe_class_id) a join #employee b on a.archives_id=b.archives_id
join #pe_class c on a.pe_class_id=c.pe_class_id
join #dept d on b.dept_id=d.dept_id
where d.dept_name='CSDN财务部'
order by a.archives_id,c.class_namego
drop table #pepara,#pe_class,#pe,#dept,#employee