采取之前的答案中的代码，并将其从函数转换为查询，可以得到：
Oracle安装程序 ：
CREATE TABLE Holidays ( HolidayDates ) ASSELECt DATE '2018-12-25' FROM DUAL UNIOn ALLSELECt DATE '2018-12-31' FROM DUAL;CREATE TABLE table_name ( start_date, end_date ) ASSELECt DATE '2018-12-21', DATE '2018-12-26' FROM DUAL UNIOn ALLSELECt DATE '2018-12-28', DATE '2019-01-01' FROM DUAL;
查询 ：
SELECt t.*, ( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7 + LEAST( TRUNC( end_date ) - TRUNC( end_date, 'IW' ) + 1, 5 ) - LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 ) - ( SELECT COUNT(1)FROM holidaysWHERe HolidayDates BETWEEN t.start_date AND t.end_date-- Exclude any weekend holidays so we don't double count.AND TRUNC( HolidayDates ) - TRUNC( HolidayDates, 'IW' ) <= 5 ) AS Num_Week_DaysFROM table_name t;
输出 ：
START_DATE | END_DATE | NUM_WEEK_DAYS:--------- | :-------- | ------------:21-DEC-18 | 26-DEC-18 | 328-DEC-18 | 01-JAN-19 | 201-JAN-19 | 07-JAN-19 | 5
db
<>在这里拨弄
