需求： 日历表 TCALENDAR_DATES，工作日flag=‘1’，非工作日=‘0’，取任一查询日期最近工作日，及最近工作日前n个工作日

日历表TCALENDAR_DATES样式

SELECT T.BASE_DATE, T.CAL_DAY, C.LAST_3_DAYFROM (SELECT C1.CAL_DAY AS BASE_DATE, MAX(C2.CAL_DAY) AS CAL_DAY --最近工作日FROM XIR_MD.TCALENDAR_DATES C1LEFT JOIN XIR_MD.TCALENDAR_DATES C2ON (C1.CAL_DAY >= C2.CAL_DAY AND C2.CAL_FLAG = '1')WHERE C1.CAL_CODE = 'CHINA_IB'AND C1.CAL_DAY >= '2020-01-01'AND C1.CAL_DAY <= '2020-12-31'GROUP BY C1.CAL_DAYORDER BY C1.CAL_DAY) TLEFT JOIN (SELECT C.CAL_DAY,LAG(C.CAL_DAY, 3, '') OVER(ORDER BY C.CAL_DAY) AS LAST_3_DAY --最近工作日 前3个工作日FROM XIR_MD.TCALENDAR_DATES CWHERE C.CAL_CODE = 'CHINA_IB'AND C.CAL_FLAG = '1'AND C.CAL_DAY >= '2020-01-01'AND C.CAL_DAY <= '2020-12-31') CON T.CAL_DAY = C.CAL_DAY

2022-11-05 修改为新方式

SELECT A.CAL_DAY, A.CAL_FLAG,LEAD(A.CAL_DAY_NEW IGNORE NULLS, 1) OVER(PARTITION BY A.CAL_CODE ORDER BY A.CAL_DAY) AS END_1_DATE,LEAD(A.CAL_DAY_NEW IGNORE NULLS, 5) OVER(PARTITION BY A.CAL_CODE ORDER BY A.CAL_DAY) AS END_5_DATEFROM (SELECT DA.CAL_DAY,DECODE(DA.CAL_FLAG, 0, NULL, DA.CAL_DAY) AS CAL_DAY_NEW,DA.CAL_FLAG,DA.CAL_CODEFROM XIR_MD.TCALENDAR_DATES DAWHERE DA.CAL_CODE = 'CHINA_IB'AND DA.CAL_DAY >= '2022-01-01'AND DA.CAL_DAY <= '2022-12-31') A

查询结果