持久化框架：MybatisPlus
因最近业务需要，很多统计功能模块要求在以前查询所有数据的情况下提供一个只查询收费后不及时结账的单选框，选中此单选框后只查询出未及时结账的数据（正常工作日周一至周四收的费在第二天24点前未及时结账算不及时；周五、周六、周日的在下周一24点前未结账算不及时；节假日及放假前一天的在收假后第一天未结账的算不及时）；但查询收费的数据是OracleDB，因在Oracle只有查询权限且是从视图中查询，所以我把节假日存在MySQLDB中，如下：
。
因前台查询收费数据详情时都是查的一个月，所以每次在查询未及时结账的数据前，先把本月、上月、下月内的节假日数据查到List里作为参数，sql如下：
//查询节假日表数据（查传入月份的上月，本月，下月的节假日数据），DB是MySQL @Select("select DATE_FORMAT(KSRQ,'%Y-%m-%d') ksrq,DATE_FORMAT(JSRQ,'%Y-%m-%d') jsrq,TS ts from dz_jjr " + "where DATE_FORMAT(KSRQ,'%Y-%m')=#{month} or DATE_FORMAT(JSRQ,'%Y-%m')=#{month} " + "or DATE_FORMAT(KSRQ,'%Y-%m')=DATE_FORMAT(DATE_SUB(#{mp1},INTERVAL 1 MONTH),'%Y-%m') or DATE_FORMAT(KSRQ,'%Y-%m')=DATE_FORMAT(DATE_SUB(#{mp1},INTERVAL 1 MONTH),'%Y-%m')" + "or DATE_FORMAT(KSRQ,'%Y-%m')=DATE_FORMAT(DATE_SUB(#{mp1},INTERVAL -1 MONTH),'%Y-%m') or DATE_FORMAT(KSRQ,'%Y-%m')=DATE_FORMAT(DATE_SUB(#{mp1},INTERVAL -1 MONTH),'%Y-%m')") public List<Map<String,Object>> getJjr(String month,String mp1);//（"2022-01","2022-01-01"）;mp1是月份的一号
然后去Oracle查询未及时结账的收费数据详情：
1 <!--新加参数说明： 2 sssj表示收款的实收时间、(SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm})表示结账时间 3 wjs：表示未及时日结、jjr MySQL平台查到的节假日、ksrq 节假日开始日期、jsrq 节假日结束日期、jsrqP1 节假日结束日期+1（即收假日期） 4 -->
第一种sql：没验证过，感觉查询的时候假如有至少两个节假日且本月数据在两个假期内都有，sql把第一个假期开始、结束时间拼到sql上后不会再把第二个假期拼上去 <select id="getRjtjjslInfo" resultType="map">
SELECT (SELECT REPLACE(zzmc,'德玛西亚有限责任公司','') FROM PPP_JJJ.xt_zz WHERE zzbm=DECODE(b.zzlxdm,'5',b.sjzzbm,b.zzbm)) DWBM_MC, REPLACE(b.zzmc,'德玛西亚有限责任公司','') GDDWBM_MC,(SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='JFQDDM' AND dmbm=a.jfqd) SFQD, (SELECT REPLACE(zzmc,'德玛西亚有限责任公司','') FROM PPP_JJJ.xt_zz WHERE zzbm=a.sfrbm) SFRBM_MC, (SELECT rymc FROM PPP_JJJ.xt_ry WHERE rybs=a.ssrbs) SSRBS_MC,a.sssj SSSJ,a.zzsj ZZSJ, (SELECT max(rjsj) FROM PPP_JJJ.zw_zzxx WHERE zzrbs=a.zzrbs AND zzsj=a.zzsj AND dqbm=#{conditionMap.dqbm}) JZSJ, (SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) RJTJSJ, (SELECT DECODE(sbbz,'1','是','否') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs) SFSB, (CASE WHEN (SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm} AND sbbz='1') IS NULL THEN '未统计上报' ELSE to_char(ROUND((SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm})-a.sssj,0)) END) TJTS, (SELECT max((SELECT rymc FROM PPP_JJJ.xt_ry WHERE rybs=rjrbs)) FROM PPP_JJJ.zw_zzxx WHERE zzrbs=a.zzrbs AND zzsj=a.zzsj AND dqbm=#{conditionMap.dqbm}) JZR, (SELECT (SELECT rymc FROM PPP_JJJ.xt_ry WHERE rybs=sqrbs) FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) TJR, a.ssze SFZE,(SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='ZIJLXDM' AND dmbm=a.zjlxdm) ZJLX, (SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='JYFSDM' AND dmbm=a.jyfsdm) JYFS,a.jshh JSHH,a.jshmc JSHMC,a.yhbh YHBH, a.yhmc YHMC,a.jldbh JLDBH,(SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='YSYWLBDM' AND dmbm=a.ywlbdm) YWLB,nvl(a.ssys,0) SSYS, nvl(a.sszkzyys,0) SSZKZYYH,nvl(a.ssdf,0) SSDF,nvl(a.sswyj,0) SSWYJ,nvl(a.ssqt,0) SSQT,nvl(a.sshzhxje,0) SSHZ FROM PPP_JJJ.zw_ssdfjl a,PPP_JJJ.xt_zz b WHERE a.gddwbm=b.zzbm AND a.dqbm=#{conditionMap.dqbm} <if test="conditionMap.dwbm!=null and conditionMap.dwbm!=''"> AND DECODE(b.zzlxdm,'5',b.sjzzbm,b.zzbm)=#{conditionMap.dwbm} </if> <if test="conditionMap.gddwbm!=null and conditionMap.gddwbm!=''"> AND a.gddwbm LIKE CONCAT(#{conditionMap.gddwbm},'%') </if> <if test="conditionMap.jfqd!=null and conditionMap.jfqd!=''"> AND a.jfqd=#{conditionMap.jfqd} </if> <if test="conditionMap.sfrbm!=null and conditionMap.sfrbm!=''"> AND a.sfrbm=#{conditionMap.sfrbm} </if> <if test="conditionMap.ssrbs!=null and conditionMap.ssrbs!=''"> AND a.ssrbs=#{conditionMap.ssrbs} </if> <if test="conditionMap.dayStart!=null and conditionMap.dayStart!='' and conditionMap.dayEnd!=null and conditionMap.dayEnd!=''"> AND a.sssj BETWEEN to_date(#{conditionMap.dayStart},'yyyy-mm-dd hh24:mi:ss') AND to_date(#{conditionMap.dayEnd},'yyyy-mm-dd hh24:mi:ss') </if> <if test="wjs=='1'.toString()">//这里表示前台勾选未及时（只查不及时数据） <choose> <when test="jjr.size()>0">//当去节假日表查到上月 本月 下月的节假日数据 and (case
//
when(not exists(SELECT 1 FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm})) then 1 when((SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) is null) then 1
//这个when表示sssj在具体某个节假日内且没在收假后第一天完成-->不及时 （如sssj【实收时间】='2022-10-02'，(SELECT
//to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm})【日结时间】='2022-10-10' ） when(TO_CHAR(a.sssj,'YYYY-MM-DD') BETWEEN ( select ksrq from (//因下面通过sssj在ksrq和jsrq之间把tem表中的数据确定到一条了，所以这里的ksrq是一个唯一单个日期 <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq, #{item.jsrq} as jsrq FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq ) and ( select jsrq from (//因下面通过sssj在ksrq和jsrq之间把tem表中的数据确定到一条了，所以这里的jsrq是一个唯一单个日期 <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all">//用虚拟表dual把List里面的节假日一条一条的放到临时表tem中 SELECT
//因节假日前一天和假期内的数据只要收假后第一天都算及时,所以这里为假期开始日期减一（例：2022-10-01 -1） to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq, #{item.jsrq} as jsrq FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq//通过sssj在ksrq和jsrq之间把tem表中的数据确定到一条 ) and ((SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > ( select jsrqP1 from ( <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq,
#{item.jsrq} as jsrq, to_char(TO_DATE(#{item.jsrq},'YYYY-MM-DD')+1,'YYYY-MM-DD') as jsrqP1 FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq )))then 1 //这个when表示节假日收假前就完成（假期加班或者放假前一天的工作当天就完成迎接一个愉快的假期，即实收时间和日结时间都在假期前一天到收假
//前），算及时 （如sssj【实收时间】='2022-10-02'，(SELECT
//to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND
//dqbm=#{conditionMap.dqbm})【日结时间】='2022-10-03' ） when(TO_CHAR(a.sssj,'YYYY-MM-DD') BETWEEN ( select ksrq from ( <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq, #{item.jsrq} as jsrq FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq ) and ( select jsrq from ( <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq, #{item.jsrq} as jsrq FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq ) and ((SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) BETWEEN ( select ksrq from ( <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq, #{item.jsrq} as jsrq FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq ) and ( select jsrq from ( <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrq, #{item.jsrq} as jsrq FROM dual </foreach> ) tem where TO_CHAR(a.SSSJ,'YYYY-MM-DD') between tem.ksrq and tem.jsrq )) )then 2
//这个when or前面表示sssj是周五周六（oracle一个周是周日至周一，即2022年01月15日为周六，本周结束的一天，但2022年01月16日为周日，
//下周开始的第一天;），周五周六在下周一内未日结完成就算不及时
when ((TO_CHAR(a.sssj,'D') in ('7','6') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > TO_CHAR(trunc(TO_DATE(TO_CHAR(a.sssj,'YYYY-MM-DD'),'YYYY-MM-DD')+7,'d') + 1,'YYYY-MM-DD')) //这个or后面的表示sssj是周一到周四，没在第二天之内日结完成都不算及时 or (TO_CHAR(a.sssj,'D') in ('1','2','3','4','5') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > to_char(a.sssj+1,'YYYY-MM-DD'))) then 1 else 2 END ) = 1//then 1 就是不及时，其他就及时 </when> <otherwise>//当去节假日表没查到上月 本月 下月的节假日数据 and (not exists(SELECT 1 FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) or (SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) is null or (TO_CHAR(a.sssj,'D') in ('7','6') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > TO_CHAR(trunc(TO_DATE(TO_CHAR(a.sssj,'YYYY-MM-DD'),'YYYY-MM-DD')+7,'d') + 1,'YYYY-MM-DD')) or (TO_CHAR(a.sssj,'D') in ('1','2','3','4','5') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > to_char(a.sssj+1,'YYYY-MM-DD'))) </otherwise> </choose> </if> and a.sssj >= trunc(to_date(#{conditionMap.month},'yyyy-mm'), 'mm') and trunc(ADD_MONTHS(to_date(#{conditionMap.month},'yyyy-mm'), 1), 'mm')>a.sssj AND a.zjlxdm NOT IN ('22','26','27') AND a.ywlbdm NOT IN ('1200','1201') AND nvl(a.ywzt,'0')='0' ORDER BY DECODE(b.zzlxdm,'5',b.sjzzbm,b.zzbm),a.gddwbm,a.jfqd,a.sfrbm,a.ssrbs,a.sssj,a.jshh,a.yhbh,a.jldbh </select>
所以我写了第二种sql：
<select id="getRjtjjslInfo_bak" resultType="map"> SELECT * from (SELECT a.SSZWLSH SSZWLSH,a.YSZWLSH YSZWLSH,(SELECT REPLACE(zzmc,'德玛西亚有限责任公司','') FROM PPP_JJJ.xt_zz WHERE zzbm=DECODE(b.zzlxdm,'5',b.sjzzbm,b.zzbm)) DWBM_MC, REPLACE(b.zzmc,'德玛西亚有限责任公司','') GDDWBM_MC,(SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='JFQDDM' AND dmbm=a.jfqd) SFQD, (SELECT REPLACE(zzmc,'德玛西亚有限责任公司','') FROM PPP_JJJ.xt_zz WHERE zzbm=a.sfrbm) SFRBM_MC, (SELECT rymc FROM PPP_JJJ.xt_ry WHERE rybs=a.ssrbs) SSRBS_MC,a.sssj SSSJ,a.zzsj ZZSJ, (SELECT max(rjsj) FROM PPP_JJJ.zw_zzxx WHERE zzrbs=a.zzrbs AND zzsj=a.zzsj AND dqbm=#{conditionMap.dqbm}) JZSJ, (SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) RJTJSJ, (SELECT DECODE(sbbz,'1','是','否') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs) SFSB, (CASE WHEN (SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm} AND sbbz='1') IS NULL THEN '未统计上报' ELSE to_char(ROUND((SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm})-a.sssj,0)) END) TJTS, (SELECT max((SELECT rymc FROM PPP_JJJ.xt_ry WHERE rybs=rjrbs)) FROM PPP_JJJ.zw_zzxx WHERE zzrbs=a.zzrbs AND zzsj=a.zzsj AND dqbm=#{conditionMap.dqbm}) JZR, (SELECT (SELECT rymc FROM PPP_JJJ.xt_ry WHERE rybs=sqrbs) FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) TJR, a.ssze SFZE,(SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='ZIJLXDM' AND dmbm=a.zjlxdm) ZJLX, (SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='JYFSDM' AND dmbm=a.jyfsdm) JYFS,a.jshh JSHH,a.jshmc JSHMC,a.yhbh YHBH, a.yhmc YHMC,a.jldbh JLDBH,(SELECT dmbmmc FROM PPP_JJJ.xt_dmbm WHERE dmbmnm='YSYWLBDM' AND dmbm=a.ywlbdm) YWLB,nvl(a.ssys,0) SSYS, nvl(a.sszkzyys,0) SSZKZYYH,nvl(a.ssdf,0) SSDF,nvl(a.sswyj,0) SSWYJ,nvl(a.ssqt,0) SSQT,nvl(a.sshzhxje,0) SSHZ ,row_number()over(partition by//此函数可用来去重；先order by后pattition（把排序后的结果按partiton by后面的条件分组） a.jldbh,a.SSZWLSH,a.YSZWLSH ORDER BY a.jldbh,a.SSZWLSH,a.YSZWLSH ) rank FROM PPP_JJJ.zw_ssdfjl a,PPP_JJJ.xt_zz b <if test="wjs=='1'.toString() and jjr.size()>0"> ,(select ksrq,jsrq,ksrqD1,jsrqP1 from ( <foreach collection="jjr" index="index" item="item" open="" close="" separator="union all"> SELECT to_char(TO_DATE(#{item.ksrq},'YYYY-MM-DD')-1,'YYYY-MM-DD') as ksrqD1, #{item.ksrq} as ksrq, #{item.jsrq} as jsrq, to_char(TO_DATE(#{item.jsrq},'YYYY-MM-DD')+1,'YYYY-MM-DD') as jsrqP1 FROM dual </foreach> ) ) tem </if> WHERE a.gddwbm=b.zzbm AND a.dqbm=#{conditionMap.dqbm} <if test="conditionMap.dwbm!=null and conditionMap.dwbm!=''"> AND DECODE(b.zzlxdm,'5',b.sjzzbm,b.zzbm)=#{conditionMap.dwbm} </if> <if test="conditionMap.gddwbm!=null and conditionMap.gddwbm!=''"> AND a.gddwbm LIKE CONCAT(#{conditionMap.gddwbm},'%') </if> <if test="conditionMap.jfqd!=null and conditionMap.jfqd!=''"> AND a.jfqd=#{conditionMap.jfqd} </if> <if test="conditionMap.sfrbm!=null and conditionMap.sfrbm!=''"> AND a.sfrbm=#{conditionMap.sfrbm} </if> <if test="conditionMap.ssrbs!=null and conditionMap.ssrbs!=''"> AND a.ssrbs=#{conditionMap.ssrbs} </if> <if test="conditionMap.dayStart!=null and conditionMap.dayStart!='' and conditionMap.dayEnd!=null and conditionMap.dayEnd!=''"> AND a.sssj BETWEEN to_date(#{conditionMap.dayStart},'yyyy-mm-dd hh24:mi:ss') AND to_date(#{conditionMap.dayEnd},'yyyy-mm-dd hh24:mi:ss') </if> <if test="wjs=='1'.toString()"> <choose> <when test="jjr.size()>0"> and (case when(not exists(SELECT 1 FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm})) then 1 when((SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) is null) then 1 when(TO_CHAR(a.sssj,'YYYY-MM-DD') BETWEEN tem.ksrqD1 and tem.jsrq and ((SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > tem.jsrqP1 ))then 1 when(TO_CHAR(a.sssj,'YYYY-MM-DD') BETWEEN tem.ksrqD1 and tem.jsrq and ((SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) BETWEEN tem.ksrqD1 and tem.jsrq ) )then 2 when ((TO_CHAR(a.sssj,'D') in ('7','6') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > TO_CHAR(trunc(TO_DATE(TO_CHAR(a.sssj,'YYYY-MM-DD'),'YYYY-MM-DD')+7,'d') + 1,'YYYY-MM-DD')) or (TO_CHAR(a.sssj,'D') in ('1','2','3','4','5') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > to_char(a.sssj+1,'YYYY-MM-DD'))) then 1 else 2 END ) = 1 </when> <otherwise> and (not exists(SELECT 1 FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) or (SELECT sqrq FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) is null or (TO_CHAR(a.sssj,'D') in ('7','6') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > TO_CHAR(trunc(TO_DATE(TO_CHAR(a.sssj,'YYYY-MM-DD'),'YYYY-MM-DD')+7,'d') + 1,'YYYY-MM-DD')) or (TO_CHAR(a.sssj,'D') in ('1','2','3','4','5') and (SELECT to_char(sqrq,'YYYY-MM-DD') FROM PPP_JJJ.zw_yxsssb WHERE dzlszh=a.rjqrbs AND dqbm=#{conditionMap.dqbm}) > to_char(a.sssj+1,'YYYY-MM-DD'))) </otherwise> </choose> </if> and a.sssj >= trunc(to_date(#{conditionMap.month},'yyyy-mm'), 'mm') and trunc(ADD_MONTHS(to_date(#{conditionMap.month},'yyyy-mm'), 1), 'mm')>a.sssj AND a.zjlxdm NOT IN ('22','26','27') AND a.ywlbdm NOT IN ('1200','1201') AND nvl(a.ywzt,'0')='0' ORDER BY DECODE(b.zzlxdm,'5',b.sjzzbm,b.zzbm),a.gddwbm,a.jfqd,a.sfrbm,a.ssrbs,a.sssj,a.jshh,a.yhbh,a.jldbh) where 2>rank </select>
效果图：
全部.png
不及时.png
提示：如果有Oracle的建表权限和插入权限就可以直接连表查询即可。
oracle一些星期的知识： SELECT to_char(sysdate-1,'YYYY-MM-DD') as ksrq from dual;--当前日期-1 SELECT to_char(sysdate+1,'YYYY-MM-DD') as ksrq from dual;--当前日期+1 SELECT to_char(sssj+1,'YYYY-MM-DD') as ksrq from PPP_JJJ.ZW_SSDFJL where SSSJ=to_date('2021-09-05 08:10:02','yyyy-mm-dd hh24:mi:ss') ;--当前日期+1 -- oracle 一个周（周日1 周一2 周二3 周三4 周四5 周五6 周六7）是从周日开始到周六，即周日就是下周开始的第一天 SELECT to_char(to_date(SYSDATE,'YYYY-MM-DD'),'D') from dual;-- 周六结果为7，周日结果为1，周一为2 SELECT to_char(to_date(SYSDATE,'YYYY-MM-DD'),'D') from dual;-- 周六结果为7，周日结果为1，周一为2 SELECT to_char(to_date('2022-01-10','YYYY-MM-DD'),'D') from dual;-- 周六结果为7，周日结果为1 SELECT to_char(to_date('2022-01-10','YYYY-MM-DD'),'D') from dual;-- 周六结果为7，周日结果为1 SELECT to_char(to_date('2022-01-12','YYYY-MM-DD'),'DAY') from dual;-- 周六结果为SATURDAY，周日结果为SUNDAY SELECT to_char(SYSDATE,'yyyy-mm-dd') SELECT TO_CHAR(TO_DATE('2022-01-16','YYYY-MM-DD'),'D') parse,TO_DATE('2022-01-16','YYYY-MM-DD')+7 xiazhou from dual where TO_CHAR(TO_DATE('2022-01-16','YYYY-MM-DD'),'D') in ('1','7','6');--获取当天是周几，下周的今天的日期 SELECT TO_CHAR(TO_DATE('2022-01-16','YYYY-MM-DD'),'D') parse,TO_DATE('2022-01-16','YYYY-MM-DD')+7 xiazhou,trunc(TO_DATE('2022-01-16','YYYY-MM-DD'),'d') + 1 benzhouyi from dual where TO_CHAR(TO_DATE('2022-01-16','YYYY-MM-DD'),'D') in ('1','7','6');--获取当天是周几，下周的今天的日期，本周的周一 SELECT TO_CHAR(TO_DATE('2022-01-15','YYYY-MM-DD'),'D') parse,TO_DATE('2022-01-15','YYYY-MM-DD')+7 xiazhou,trunc(TO_DATE('2022-01-15','YYYY-MM-DD'),'d') + 1 benzhouyi from dual where TO_CHAR(TO_DATE('2022-01-15','YYYY-MM-DD'),'D') in ('1','7','6');--获取当天是周几，下周的今天的日期，本周的周一 SELECT trunc(SYSDATE,'d') + 1 from dual;-- 获取当前时间的周一日期 SELECT trunc(to_date('2022-01-12','yyyy-mm-dd'),'d') + 1 from dual;-- 获取当前时间的周一日期 SELECT trunc(to_date(sssj,'yyyy-mm-dd'),'d') + 1 from PPP_JJJ.ZW_SSDFJL where SSSJ=to_date('2021-09-05 08:10:02','yyyy-mm-dd hh24:mi:ss') ; SELECT trunc(to_date(to_char(sssj,'yyyy-mm-dd'),'yyyy-mm-dd'),'d') + 1 from PPP_JJJ.ZW_SSDFJL where SSSJ=to_date('2021-09-05 08:10:02','yyyy-mm-dd hh24:mi:ss') ;-- 本周一 SELECT trunc(to_date(to_char(sssj,'yyyy-mm-dd'),'yyyy-mm-dd'),'d') + 1 本周一,TO_DATE(TO_CHAR(sssj,'YYYY-MM-DD'),'YYYY-MM-DD')+7 下周今天 ,trunc(TO_DATE(TO_CHAR(sssj,'YYYY-MM-DD'),'YYYY-MM-DD')+7,'d') + 1 下周一 from PPP_JJJ.ZW_SSDFJL where SSSJ=to_date('2021-09-05 08:10:02','yyyy-mm-dd hh24:mi:ss') ;-- 一周（日 1，一 2，二 3，三 4，四 5，五 6，六 7）