| 如下图，年度积分统计生产员工报表中，数据集sql调用视图，报表查出来生产员工数量跟后面的奖惩条目来的，在前面的流程中录入数据的时候，如果有员工在一年内没有任何奖惩，在报表中将不显示。 现在的问题就是，本来客户那有1000多号员工，结果一年里面有一部分人没有任何奖惩，查询结果只有那些有奖惩的人，客户看完说，他们公司不止这么多人，意思就是查询结果要显示全部的员工，没有奖惩的员工也显示出来，就是后面的奖惩栏目为空而已。 搞了好久，没想出解决办法，求个大神帮忙，看能不能把前面4列显示死的放在那，这样是不是要再弄个数据集取数据，我试着做了一次，结果在查询的时候出现报表计算超时的错误。 求大神帮忙解答。 这是视图： CREATE OR REPLACE VIEW HR_EMP_JIFEN_REPORT_VIEW AS SELECT JF.OID AS "积分ID", EMP.EMPCATEGORY, ( CASE WHEN EMP.EMPCATEGORY=1 THEN '生产员工' WHEN EMP.EMPCATEGORY=2 THEN '工班长' WHEN EMP.EMPCATEGORY=3 THEN '一般管理人员' WHEN EMP.EMPCATEGORY=4 THEN '中层管理人员' END ) AS "人员类别名", JF.EMPID, EMP.EMPNAME AS "姓名", JF.DEPTID, DEP.ORGNAME AS "部门", EMP.POSTID, POST.postname AS "职务", (CASE WHEN JF.JICHU IS NULL THEN 10 ELSE JF.JICHU END ) AS "基础分", (CASE WHEN JF.KAOQINLV IS NULL THEN 1 ELSE JF.KAOQINLV END ) AS "考勤率", ((CASE WHEN JF.JICHU IS NULL THEN 10 ELSE JF.JICHU END )*(CASE WHEN JF.KAOQINLV IS NULL THEN 1 ELSE JF.KAOQINLV END )) AS "考勤分", JF.BZWDY as "班组五大员", JF.BZJSPMSCO AS "班组建设排名", JF.BZJSPMDES AS "班组建设排名DES", JF.GWXJSCO AS "岗位星级", JF.GWXJDES AS "岗位星级DES", JF.BZSBZRSCO AS "班组设备责任", JF.BZSBZRDES AS "班组设备责任DES", JF.GWTBSCO AS "公文通报", JF.GWTBDES AS "公文通报DES", JF.NDLWPXSCO AS "年度论文评选", JF.NDLWPXDES AS "年度论文评选DES", JF.SDFXSCO AS "深度分析", JF.SDFXDES AS "深度分析DES", JF.ZGZYJFSCO AS "职工作业积分", JF.ZGZYJFDES AS "职工作业积分DES", JF.ZGTNCSSCO AS "职工体能测试", JF.ZGTNCSDES AS "职工体能测试DES", JF.ZGJNBWSCO AS "职工技能比武", JF.ZGJNBWDES AS "职工技能比武DES", JF.QCGGSCO AS "QC攻关", JF.QCGGDES AS "QC攻关DES", JF.JSGGHLHJYSCO AS "技术攻关合理", JF.JSGGHLHJYDES AS "技术攻关合理DES", JF.RXLJSZDWSCO AS "入选路局三支队", JF.RXLJSZDWDES AS "入选路局三支队DES", JF.YXGBZPXSCO AS "优秀工班长评选", JF.YXGBZPXDES AS "优秀工班长评选DES", JF.BZHCJJSSCO AS "标准化车间建设", JF.BZHCJJSDES AS "标准化车间建设DES", JF.CJSBZRGZSCO AS "车间设备责任故障", JF.CJSBZRGZDES AS "车间设备责任故障DES", JF.CJBZJSSCO AS "车间班组建设", JF.CJBAJSDES AS "车间班组建设DES", JF.YZYQJSCO AS "一站一区间", JF.YZYQJDES AS "一站一区间DES", JF.XJZBGLSCO AS "新机制把关量", JF.XJZBGLDES AS "新机制把关量DES", JF.ZHIBANSCO AS "值班", JF.ZHIBANDES AS "值班DES", JF.LSXRWSCO AS "临时性任务", JF.LSXRWDES AS "临时性任务DES", JF.RYEAR, JF.RMONTH, JF.RDAY, JF.JIDU, JF.JIFENSTATUS FROM HR_EMPLOYEE emp, HR_POST post, HR_EMP_JIFEN_REPORT jf, ESORGANIZATION dep WHERE 1=1 AND JF.EMPID=EMP.OID(+) --关联员工信息 AND EMP.POSTID=POST.OID(+) --关联职位信息 AND EMP.DEPTID=DEP.OID(+) --关联部门信息 --AND JF.RYEAR=2015 --AND JF.RMONTH=6 --AND EMP.DEPTID IN (SELECT OID FROM ESORGANIZATION WHERE XPATH LIKE '%/1/%') --and EMP.EMPCATEGORY=1 ORDER BY emp.EMPCATEGORY ; 这是年份积分统计 生产员工 的数据集sql: SELECT JF.EMPCATEGORY, JF.人员类别名, JF.EMPID, JF.姓名, JF.DEPTID, JF.部门, JF.POSTID, JF.职务, SUM(JF.班组五大员) as "班组五大员", SUM(考勤分) AS "考勤分", SUM(班组建设排名) as "班组建设排名", SUM(岗位星级) as "岗位星级", SUM(班组设备责任) as "班组设备责任", SUM(公文通报) as "公文通报", SUM(年度论文评选) as "年度论文评选", SUM(深度分析) as "深度分析", SUM(职工作业积分) as "职工作业积分", SUM(职工体能测试) as "职工体能测试", SUM(职工技能比武) as "职工技能比武", SUM(QC攻关) as "QC攻关", SUM(技术攻关合理) as "技术攻关合理", SUM(入选路局三支队) as "入选路局三支队", SUM(优秀工班长评选) as "优秀工班长评选", SUM(标准化车间建设) as "标准化车间建设", SUM(车间设备责任故障) as "车间设备责任故障", SUM(车间班组建设) as "车间班组建设", SUM(一站一区间) as "一站一区间", SUM(新机制把关量) as "新机制把关量", SUM(值班) as "值班", SUM(临时性任务) as "临时性任务" FROM HR_EMP_JIFEN_REPORT_View JF WHERE 1=1 AND JF.RYEAR=${year} AND JF.DEPTID IN (SELECT OID FROM ESORGANIZATION WHERE XPATH LIKE '%/${orgid}/%') and JF.EMPCATEGORY=1 GROUP BY JF.EMPCATEGORY, JF.人员类别名, JF.EMPID, JF.姓名, JF.DEPTID, JF.部门, JF.POSTID, JF.职务 求大神帮忙！ |
最佳回答 |
||||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
