需求:把员工全部显示在积分排名里面;
select
m.id, m.empId, m.orgId, m.eveName, m.czSocre, m.czType, m.optDate, m.optType, m.operPerson,
m.checkPerson, m.jktypeid, m.jktypename, m.isprint, m.creatTime,m.remark,m.tatalA,
(select employeeName from QY_EMPLOYEE where employeeId = m.empId) employeename,
(select jobNumber from QY_EMPLOYEE where employeeId = m.empId) jobnumber,
(select departmentName from QY_ORGANIZATION where ID = (select department from QY_EMPLOYEE where employeeId = m.empId)) departmentname,
(select sum(czSocre)czjf from CZ_QUERY_RANK where orgId = 'E000102' AND isprint='1' and empId = m.empId) czSocrejf,
(select sum(czSocre)czkf from CZ_QUERY_RANK where orgId = 'E000102' AND isprint='2' and empId = m.empId) czSocrekf
from
(
select g.*,tt.tatalA from CZ_QUERY_RANK g inner join
(
SELECT x.empId,x.orgId,(x.jfa-IFNULL(b.kfa,0)) tatalA
FROM
(SELECT empId,orgId,SUM(optA) jfa FROM JF_QUERY_RANK WHERE orgId='E000102' AND scoType='1'
GROUP BY empId ) x LEFT JOIN(
SELECT empId,orgId,SUM(optA) kfa FROM JF_QUERY_RANK WHERE orgId='E000102' AND scoType='2' GROUP BY empId
) b ON x.empId=b.empId
)tt on g.empId = tt.empId group by empId
) m
where m.orgId = 'E000102'
union
select NULL AS id, employeeId as empId,enterpriseId as orgId,null AS eveName,0 AS czSocre,null AS czType,null AS optDate, null AS optType,null AS operPerson,null AS checkPerson,null AS jktypeid,
null AS jktypename,null AS isprint,null AS creatTime,null AS remark,0 AS tatalA,
employeeName as employeename,jobNumber as jobnumber,(select departmentName from QY_ORGANIZATION where ID = department) departmentname,
0 AS czSocrejf,0 AS czSocrekf from QY_EMPLOYEE where enterpriseId='E000102'
union的使用
最新推荐文章于 2022-05-18 00:18:18 发布