问题描述
create table T_temp( e_ID VARCHAR2(255 CHAR), E_CODE VARCHAR2(255 CHAR), E_NAME VARCHAR2(255 CHAR), e_POST VARCHAR2(255 CHAR), e_DEPT VARCHAR2(255 CHAR), e_TYPE VARCHAR2(255 CHAR), e_date DATE, e_DAYS NUMBER)insert into t_temp(e_id,e_code,e_name,e_dept,e_post,e_type,e_date,e_days)select '1','001','张三','财务部','经理','病假',to_date('2011-08-05','yyyy-MM-dd'),0.5 from dualunion allselect '1','001','张三','财务部','经理','事假',to_date('2011-08-25','yyyy-MM-dd'),0.5 from dualunion allselect '1','001','张三','财务部','经理','年假',to_date('2011-09-13','yyyy-MM-dd'),5 from dualunion allselect '2','002','李四','技术部','职员','事假',to_date('2011-08-14','yyyy-MM-dd'),1 from dualunion allselect '2','002','李四','技术部','职员','旷工',to_date('2011-08-20','yyyy-MM-dd'),0.5 from dual想实现如下的显示方式;[img] 编号部门姓名职务月份事假病假旷工婚假年假e001财务部张三经理一月00000e001财务部张三经理二月00000e001财务部张三经理三月00000e001财务部张三经理四月00000e001财务部张三经理五月00000e001财务部张三经理六月00000e001财务部张三经理七月00000e001财务部张三经理八月0.50.5000e001财务部张三经理九月00005e001财务部张三经理十月00000e001财务部张三经理十一月00000e001财务部张三经理十二月00000[/img] 问题补充：mymailzxj 写道
解决方案
像这样要产生每月的数据，不存在的月份数据又是0，是用SQL来实现好还是在程序里面处理好呢？ //现在sql中已经解决了不存在月份数据为0的问题了。以为这么多年电信行业经验，没有那个应用程序的处理性能能比oracle数据库处理速度还要快的。应用程序里面无非就是循环处理，大数据量会给服务器造成很大的压力。另外对于大数据量处理需要进行分页处理的。
解决方案二：
那个(+)是什么意思？ //外关联，当左边的记录在有右侧的记录中不存在时，用空记录表示。相当于标准SQL语句的左关联
解决方案三：
--给个统计所有员工的sqlSELECT 'E' || T1.E_CODE 编号,T1.E_NAME 姓名,T1.E_DEPT 部门,T1.E_POST 职位, DECODE(T.月份,B.MONTH1,T.旷工,0) 旷工,DECODE(T.月份,B.MONTH1,T.事假,0) 事假, DECODE(T.月份,B.MONTH1,T.病假,0) 病假,DECODE(T.月份,B.MONTH1,T.婚假,0) 婚假, DECODE(T.月份,B.MONTH1,T.年假,0) 年假,B.YEAR 年份,B.MONTH 月份 FROM (SELECT TO_CHAR(A.E_DATE, 'YYYYMM') ALL_YEAR, A.E_CODE 编号,A.E_NAME 姓名,A.E_DEPT 部门,A.E_POST 职位, TO_CHAR(A.E_DATE, 'YYYY') 年份,TO_CHAR(A.E_DATE, 'MM') 月份, SUM(DECODE(A.E_TYPE,'旷工',A.E_DAYS,0)) 旷工,SUM(DECODE(A.E_TYPE,'事假',A.E_DAYS,0)) 事假, SUM(DECODE(A.E_TYPE,'病假',A.E_DAYS,0)) 病假,SUM(DECODE(A.E_TYPE,'婚假',A.E_DAYS,0)) 婚假, SUM(DECODE(A.E_TYPE,'年假',A.E_DAYS,0)) 年假 FROM (SELECT DISTINCT(C.E_CODE),C.E_NAME,C.E_POST,C.E_DEPT,c.e_type,c.e_date,c.e_days FROM T_TEMP C) A GROUP BY A.E_CODE,A.E_NAME,A.E_DEPT,A.E_POST,TO_CHAR(A.E_DATE, 'YYYY'),TO_CHAR(A.E_DATE, 'MM'),TO_CHAR(A.E_DATE, 'YYYYMM')ORDER BY A.E_CODE,TO_CHAR(A.E_DATE, 'MM')) T,YEAR B,(SELECT DISTINCT(C.E_CODE),C.E_NAME,C.E_POST,C.E_DEPT FROM T_TEMP C) T1WHERE B.ALL_YEAR =T.ALL_YEAR(+)ORDER BY T1.E_CODE,B.MONTH1SELECT 'E' || T1.E_CODE 编号,T1.E_NAME 姓名,T1.E_DEPT 部门,T1.E_POST 职位, DECODE(T.月份,B.MONTH1,T.旷工,0) 旷工,DECODE(T.月份,B.MONTH1,T.事假,0) 事假, DECODE(T.月份,B.MONTH1,T.病假,0) 病假,DECODE(T.月份,B.MONTH1,T.婚假,0) 婚假, DECODE(T.月份,B.MONTH1,T.年假,0) 年假,B.YEAR 年份,B.MONTH 月份 FROM (SELECT TO_CHAR(A.E_DATE, 'YYYYMM') ALL_YEAR, A.E_CODE 编号,A.E_NAME 姓名,A.E_DEPT 部门,A.E_POST 职位, TO_CHAR(A.E_DATE, 'YYYY') 年份,TO_CHAR(A.E_DATE, 'MM') 月份, SUM(DECODE(A.E_TYPE,'旷工',A.E_DAYS,0)) 旷工,SUM(DECODE(A.E_TYPE,'事假',A.E_DAYS,0)) 事假, SUM(DECODE(A.E_TYPE,'病假',A.E_DAYS,0)) 病假,SUM(DECODE(A.E_TYPE,'婚假',A.E_DAYS,0)) 婚假, SUM(DECODE(A.E_TYPE,'年假',A.E_DAYS,0)) 年假 FROM (SELECT DISTINCT(C.E_CODE),C.E_NAME,C.E_POST,C.E_DEPT,c.e_type,c.e_date,c.e_days FROM T_TEMP C) A GROUP BY A.E_CODE,A.E_NAME,A.E_DEPT,A.E_POST,TO_CHAR(A.E_DATE, 'YYYY'),TO_CHAR(A.E_DATE, 'MM'),TO_CHAR(A.E_DATE, 'YYYYMM')ORDER BY A.E_CODE,TO_CHAR(A.E_DATE, 'MM')) T,YEAR B,(SELECT DISTINCT(C.E_CODE),C.E_NAME,C.E_POST,C.E_DEPT FROM T_TEMP C) T1WHERE B.ALL_YEAR =T.ALL_YEAR(+)ORDER BY T1.E_CODE,B.MONTH1
解决方案四：
prompt PL/SQL Developer import fileprompt Created on 2011年8月22日 by zxjset feedback offset define offprompt Creating YEAR...create table YEAR( ALL_YEAR VARCHAR2(6), YEAR VARCHAR2(4), MONTH VARCHAR2(8), MONTH1 VARCHAR2(8));prompt Disabling triggers for YEAR...alter table YEAR disable all triggers;prompt Deleting YEAR...delete from YEAR;commit;prompt Loading YEAR...insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201101', '2011', '一月', '01');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201102', '2011', '二月', '02');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201103', '2011', '三月', '03');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201104', '2011', '四月', '04');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201105', '2011', '五月', '05');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201106', '2011', '六月', '06');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201107', '2011', '七月', '07');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201108', '2011', '八月', '08');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201109', '2011', '九月', '09');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201110', '2011', '十月', '10');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201111', '2011', '十一月', '11');insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)values ('201112', '2011', '十二月', '12');commit;prompt 12 records loadedprompt Enabling triggers for YEAR...alter table YEAR enable all triggers;set feedback onset define onprompt Done.--先在oracle数据库中建立表，并插入相关数据SELECT 'E' || T1.E_CODE 编号,T1.E_NAME 姓名,T1.E_DEPT 部门,T1.E_POST 职位, DECODE(T.月份,B.MONTH1,T.旷工,0) 旷工,DECODE(T.月份,B.MONTH1,T.事假,0) 事假, DECODE(T.月份,B.MONTH1,T.病假,0) 病假,DECODE(T.月份,B.MONTH1,T.婚假,0) 婚假, DECODE(T.月份,B.MONTH1,T.年假,0) 年假,B.YEAR 年份,B.MONTH 月份 FROM (SELECT TO_CHAR(A.E_DATE, 'YYYYMM') ALL_YEAR, A.E_CODE 编号,A.E_NAME 姓名,A.E_DEPT 部门,A.E_POST 职位, TO_CHAR(A.E_DATE, 'YYYY') 年份,TO_CHAR(A.E_DATE, 'MM') 月份, SUM(DECODE(A.E_TYPE,'旷工',A.E_DAYS,0)) 旷工,SUM(DECODE(A.E_TYPE,'事假',A.E_DAYS,0)) 事假, SUM(DECODE(A.E_TYPE,'病假',A.E_DAYS,0)) 病假,SUM(DECODE(A.E_TYPE,'婚假',A.E_DAYS,0)) 婚假, SUM(DECODE(A.E_TYPE,'年假',A.E_DAYS,0)) 年假 FROM T_TEMP A WHERE A.E_CODE = '001' --员工编号 GROUP BY A.E_CODE,A.E_NAME,A.E_DEPT,A.E_POST,TO_CHAR(A.E_DATE, 'YYYY'),TO_CHAR(A.E_DATE, 'MM'),TO_CHAR(A.E_DATE, 'YYYYMM')ORDER BY A.E_CODE,TO_CHAR(A.E_DATE, 'MM')) T,YEAR B,(SELECT DISTINCT(C.E_CODE),C.E_NAME,C.E_POST,C.E_DEPT FROM T_TEMP C) T1WHERE B.ALL_YEAR =T.ALL_YEAR(+) AND T1.E_CODE= '001' --员工编号
解决方案五：
拆分成多个表，一个是职工基本信息，另一个是关于请假的信息，查询的时候做个表连接就可以了