日常工作的sql

本文主要是介绍日常工作的sql，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

                select * from (select re.recruit_name,
                re.emp_no,
                re.psDeptname,
                re.branch_name2,
                re.branch_name3,
                re.branch_name4,
                to_char(ci.check_time, 'yyyy/mm/dd hh24:mi') check_time,
                re.checkin_name,
                decode(ci.checked, null, '未打卡', 'N', '未打卡', 'Y','已打卡', 'R', '代打卡') checked,
                ci.checked checkin_state,
                ci.remark,
                ci.operator
                from (select r.recruit_name,
                ''  psDeptname,
                tt.emp_no,
                decode(br.branch_code2,
                null,
                decode(r.branch_code, '86', '总公司', null),
                br.branch_name2) branch_name2,
                br.branch_name3,
                br.branch_name4,
                cr.checkin_name,
                cr.rule_no,
                cr.start_time
                from checkin_rule    cr,
                train_trainee   tt,
                recruit         r,
                branch_relation br
                where cr.is_valid = 'Y'
                and tt.is_valid = 'Y'
                and r.is_valid = 'Y'
                and cr.train_no = #{marketCheckInBO.trainNo}
                <if test="marketCheckInBO.ruleNo != null and marketCheckInBO.ruleNo != ''">
                    and cr.rule_no = #{marketCheckInBO.ruleNo}
                </if>
                <if test="marketCheckInBO.checkinSeqList != null and marketCheckInBO.checkinSeqList.size > 0">
                    and cr.checkin_seq in
                    <foreach collection="marketCheckInBO.checkinSeqList" item="seq" open="(" close=")" separator=",">
                        #{seq}
                    </foreach>
                </if>
                and tt.trainee_type = 3
                <if test="marketCheckInBO.branchCode != null and marketCheckInBO.branchCode != ''">
                    and exists (select 1
                    from branch_info bi
                    where r.branch_code = bi.branch_code
                    start with bi.branch_code = #{marketCheckInBO.branchCode}
                    connect by prior bi.branch_code = bi.parent_branch)
                </if>
                <if test="marketCheckInBO.recruitName != null and marketCheckInBO.recruitName != ''">
                    and r.recruit_name like '%'||#{marketCheckInBO.recruitName}||'%'
                </if>
                <if test="marketCheckInBO.empNo != null and marketCheckInBO.empNo != ''">
                    and r.id_no = #{marketCheckInBO.empNo}
                </if>
                and tt.train_no = cr.train_no
                and r.id_no = tt.emp_no
                and br.branch_code = r.branch_code) re,
                check_in ci
                where ci.is_valid(+) = 'Y'
                and ci.rule_no(+) = re.rule_no
                and ci.emp_no(+) = re.emp_no

                order by re.start_time   desc,
                ci.checked      asc,
                ci.check_time   desc,
                re.branch_name4 asc,
                re.recruit_name asc
                ) t



                union

                select * from (select re.recruit_name,
                re.emp_no,
                re.psDeptname,
                re.branch_name2,
                re.branch_name3,
                re.branch_name4,
                to_char(ci.check_time, 'yyyy/mm/dd hh24:mi') check_time,
                re.checkin_name,
                decode(ci.checked, null, '未打卡', 'N', '未打卡', 'Y','已打卡', 'R', '代打卡') checked,
                ci.checked checkin_state,
                ci.remark,
                ci.operator
                from (select st.emp_name recruit_name,
                ''  psDeptname,
                tt.emp_no,
                br.branch_name2,
                br.branch_name3,
                br.branch_name4,
                cr.checkin_name,
                cr.rule_no,
                cr.start_time
                from checkin_rule  cr,train_trainee tt,staff_info st left join dept_info di on st.department_no = di.dept_no
                left join branch_relation br on di.branch_code = br.branch_code
                where cr.is_valid = 'Y'
                and tt.is_valid = 'Y'
                and st.is_valid = 'Y'
                and cr.train_no = #{marketCheckInBO.trainNo}

                <if test="marketCheckInBO.branchCode != null and marketCheckInBO.branchCode != ''">
                    AND br.branch_code IN
                    (SELECT
                    bi.branch_code
                    FROM branch_info bi
                    START WITH bi.branch_code = #{marketCheckInBO.branchCode}
                    CONNECT BY PRIOR bi.branch_code = bi.parent_branch)
                </if>
                <if test="marketCheckInBO.recruitName != null and marketCheckInBO.recruitName != ''">
                    AND st.EMP_NAME LIKE '%'||#{marketCheckInBO.recruitName}||'%'
                </if>
                <if test="marketCheckInBO.empNo != null and marketCheckInBO.empNo != ''">
                    AND tt.EMP_NO = #{marketCheckInBO.empNo}
                </if>

                <if test="marketCheckInBO.ruleNo != null and marketCheckInBO.ruleNo != ''">
                    and cr.rule_no = #{marketCheckInBO.ruleNo}
                </if>
                and tt.trainee_type =1
                and tt.train_no = cr.train_no
                and tt.emp_no = st.emp_no
                ) re,
                check_in ci
                where ci.is_valid(+) = 'Y'
                and ci.rule_no(+) = re.rule_no
                and ci.emp_no(+) = re.emp_no

                order by re.start_time   desc,
                ci.checked      asc,
                ci.check_time   desc,
                re.branch_name4 asc,
                re.recruit_name asc

                )t



                union
                select * from (
                select re.recruit_name,
                re.emp_no,
                re.psDeptname,
                re.branch_name2,
                re.branch_name3,
                re.branch_name4,
                to_char(ci.check_time, 'yyyy/mm/dd hh24:mi') check_time,
                re.checkin_name,
                decode(ci.checked, null, '未打卡', 'N', '未打卡', 'Y','已打卡', 'R', '代打卡') checked,
                ci.checked checkin_state,
                ci.remark,
                ci.operator
                from (select TEMP.recruit_name,
                TEMP.emp_no,
                TEMP.psDeptname,
                ''  branch_name2,
                ''  branch_name3,
                '' branch_name4,
                cr.checkin_name,
                cr.rule_no,
                cr.start_time

                from  checkin_rule  cr,
                (select * from (
                SELECT EMPO.*,
                ROW_NUMBER() OVER(PARTITION BY EMPO.EMP_NO ORDER BY EMPO.CREATED_DATE ASC) RN
                FROM (
                select tt.EMP_NO,
                tt.CREATED_DATE,
                te.name recruit_name,
                te.psDeptname  psDeptname,
                tt.train_no
                from TRAIN_TRAINEE tt,
                (select distinct psop.emplid        emplId,
                psop.name_display  name,
                pod.ps_deptname    psDeptname

                from ps_oa_personaldata psop,
                ps_oa_job          poa,
                ps_oa_dept         pod,
                ps_oa_ad_user      poau
                where psop.emplid = poa.emplid
                and poa.deptid = pod.deptid
                and psop.emplid = poau.emplid
                and psop.is_valid_flag = 'Y'
                and poa.is_valid = 'Y'
                and pod.is_valid = 'Y'
                and poa.EMPL_RCD = '0'
                and psop.business_unit = 'SINOL'
                ) te
                where tt.IS_VALID = 'Y'
                and tt.trainee_type = '2'
                and te.emplid(+) = tt.emp_no
                <if test="marketCheckInBO.recruitName != null and marketCheckInBO.recruitName != ''">
                    AND  te.name  LIKE '%'||#{marketCheckInBO.recruitName}||'%'
                </if>

                <if test="marketCheckInBO.empNo != null and marketCheckInBO.empNo != ''">
                    AND tt.emp_no = #{marketCheckInBO.empNo}
                </if>

                <if test="marketCheckInBO.psDeptname != null and marketCheckInBO.psDeptname != ''">
                    AND  te.psDeptname  LIKE '%'||#{marketCheckInBO.psDeptname}||'%'
                </if>

                AND tt.TRAIN_NO = #{marketCheckInBO.trainNo}

                order by tt.CREATED_DATE
                ) EMPO) PO
                WHERE RN = 1) TEMP
                where cr.is_valid = 'Y'
                and TEMP.train_no = cr.train_no
                and cr.train_no = #{marketCheckInBO.trainNo}
                <if test="marketCheckInBO.ruleNo != null and marketCheckInBO.ruleNo != ''">
                    and cr.rule_no = #{marketCheckInBO.ruleNo}
                </if>
                ) re,
                check_in ci
                where ci.is_valid(+) = 'Y'
                and ci.rule_no(+) = re.rule_no
                and ci.emp_no(+) = re.emp_no
                order by re.start_time   desc,
                ci.checked      asc,
                ci.check_time   desc,
                re.branch_name4 asc,
                re.recruit_name asc
                )t

这篇关于日常工作的sql的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！