SQL文~求高手来~~~~~~~~~~~~~~~~~~~~~~现在有两个表一个主表是 考勤信息表SQL codeCREATE TABLE HIPS_HR.H
SQL文~求高手来~~~~~~~~~~~~~~~~~~~~~~
现在有两个表
一个主表是 考勤信息表
- SQL code
CREATE TABLE HIPS_HR.HR_WORK_ATTEND( PERSONNEL_ID VARCHAR2(10) NOT NULL, FREQUENCY VARCHAR2(10) NOT NULL, WORKDAY VARCHAR2(8) NOT NULL, WORKTIME DATE, CLOSETIME DATE, OVERTIMEFLG CHAR(1) NOT NULL, UPDATE_TIME DATE, UPDATE_USERID VARCHAR2(20), CREATE_TIME DATE, CREATE_USERID VARCHAR2(20), EXCLUSIVEKEY VARCHAR2(100), CONSTRAINT WORK_ATTEND_KEY PRIMARY KEY (PERSONNEL_ID, WORKDAY, FREQUENCY) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USERS STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) LOGGING)PCTFREE 10MAXTRANS 255TABLESPACE USERSSTORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)NOCACHELOGGING/COMMENT ON TABLE HIPS_HR.HR_WORK_ATTEND IS '考勤信息表'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.PERSONNEL_ID IS '员工ID'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.FREQUENCY IS '班次'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.WORKDAY IS '工作日'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.WORKTIME IS '上班时间'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.CLOSETIME IS '下班时间'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.OVERTIMEFLG IS '是否加班 1：是 0：不是'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.UPDATE_TIME IS '更新时间'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.UPDATE_USERID IS '更新者'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.CREATE_TIME IS '创建时间'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.CREATE_USERID IS '创建者'/COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.EXCLUSIVEKEY IS '排他键'/
还有一个是请假表
- SQL code
CREATE TABLE HIPS_HR.HR_LEAVE( LEAVE_ID VARCHAR2(15) NOT NULL, PERSONNEL_ID VARCHAR2(50), FREQUENCY VARCHAR2(10), LEAVE_DATE DATE, LEAVE_LENGTH_DAY NUMBER(10,0), LEAVE_LENGTH_HOUR NUMBER(10,0), LEAVE_LENGTH_MIN NUMBER(10,0), LEAVE_KSSJ DATE, LEAVE_JSSJ DATE, LEAVE_TYPE VARCHAR2(5), LEAVE_REASON VARCHAR2(200), STATUS VARCHAR2(200), UPDATE_TIME DATE, UPDATE_USERID VARCHAR2(20), CREATE_TIME DATE, CREATE_USERID VARCHAR2(20), EXCLUSIVEKEY VARCHAR2(100) NOT NULL, CONSTRAINT HR_LEAVE_PK PRIMARY KEY (LEAVE_ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE USERS STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) LOGGING)PCTFREE 10MAXTRANS 255TABLESPACE USERSSTORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)NOCACHELOGGING/COMMENT ON TABLE HIPS_HR.HR_LEAVE IS '员工请假表'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_ID IS '请假编号'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.PERSONNEL_ID IS '用户ID'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.FREQUENCY IS '班次'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_DATE IS '请假日期'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_LENGTH_DAY IS '请假时长（天）'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_LENGTH_HOUR IS '请假时长（小时）'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_LENGTH_MIN IS '请假时长（分钟）'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_KSSJ IS '请假开始时间'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_JSSJ IS '请假结束时间'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_TYPE IS '请假类型'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_REASON IS '请假事由'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.STATUS IS '单据状态'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.UPDATE_TIME IS '更新时间'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.UPDATE_USERID IS '更新者'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.CREATE_TIME IS '创建时间'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.CREATE_USERID IS '创建者'/COMMENT ON COLUMN HIPS_HR.HR_LEAVE.EXCLUSIVEKEY IS '排他键(使用UUID进行排他)'/
现在查询想实现的是
查询到一条考勤信息的时候
那天是否请假了（对应的ID在有假条的开始结束时间内包含这一天） 并且把请假的时间计算出来
我现在写的语句left join过后会重复 、 也不知道怎么算请假时长
这个是我现在用的SQL文{用的是ibatis} SSI的框架
- SQL code
<!--考勤信息检索--> <select id="searchAttendInfo" parameterClass="AttenceEntity" resultClass="WorkAttendEntity"> select workAttend.PERSONNEL_ID as personnelId, personnel.ATTENCE_ID as attenceId, personnel.PERSONNEL_NAME as personnelName, workAttend.WORKDAY as workDay, workAttend.FREQUENCY as frequency, workAttend.WORKTIME as workTime, workAttend.CLOSETIME as closeTime, workAttend.OVERTIMEFLG as overTimeFlg, leave.LEAVE_ID as isLeave from HR_WORK_ATTEND workAttend left join HR_PERSONNEL personnel on personnel.PERSONNEL_ID = workAttend.PERSONNEL_ID left join (select count(LEAVE_ID) as LEAVE_ID,PERSONNEL_ID,LEAVE_KSSJ,LEAVE_JSSJ from hr_leave group by PERSONNEL_ID,LEAVE_KSSJ,LEAVE_JSSJ) leave on <![CDATA[workAttend.WORKDAY >= to_char(leave.LEAVE_KSSJ, 'YYYYMMDD')]]> and <![CDATA[workAttend.WORKDAY <= to_char(leave.LEAVE_JSSJ, 'YYYYMMDD')]]> and leave.PERSONNEL_ID = workAttend.PERSONNEL_ID <dynamic prepend="where"> <isNotEmpty prepend="and" property="personnelName"> personnel.PERSONNEL_NAME like '%'||#personnelName#||'%' </isNotEmpty> <isNotEmpty prepend="and" property="attendYear"> workAttend.WORKDAY like #attendYear#||'%' </isNotEmpty> <isNotEmpty prepend="and" property="attendMonth"> substr(workAttend.WORKDAY,5,2) =#attendMonth# </isNotEmpty> <isNotEmpty prepend="and" property="attendDay"> substr(workAttend.WORKDAY,7,2) =#attendDay# </isNotEmpty> <isNotEmpty prepend="and" property="personnelId"> workAttend.PERSONNEL_ID = #personnelId# </isNotEmpty> <isNotEmpty prepend="and" property="attenceId"> personnel.ATTENCE_ID = #attenceId# </isNotEmpty> </dynamic> order by workAttend.PERSONNEL_ID,workAttend.WORKDAY </select>
求指点~感激不尽、分不多见谅
[解决办法]
在查询个员工一条考勤记录时，要确定当天该员工是否请假，如果请假了要计算出请假时长？
是这个意思不？
[解决办法]
一天请假好几次，应该是请假表有好几条记录吧，而不是你说的考勤表吧？