这里有一个可能的解决方案：创建一个日期列表，然后INNER JOIN工作日表，然后通过休假表中的持续时间限制结果。

CREATE TABLE emp_leave (
  ID int
  , Employee int
  , LeaveDate date
  , Duration int
);

INSERT INTO emp_leave (ID, Employee, LeaveDate, Duration)
VALUES (1, 101, '12-30-2022', 5)
;

CREATE TABLE emp_workdays (
  ID int
  , Employee int
  , WeekNumber int
  , WorkingDayNumber int
);

INSERT INTO emp_workdays (ID, Employee, WeekNumber, WorkingDayNumber)
VALUES (1, 101, 1, 1)
  , (1, 101, 1, 6)
  , (1, 101, 1, 7)
;

--Change the start of the week to Sunday;
SET DATEFIRST 7;

--Create a list of 10 numbers
WITH x as (
  SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as x(a)
)

--Create a list of 10000 numbers
, y as ( 
  SELECT ROW_NUMBER() OVER(ORDER BY ones.a) as row_num 
  FROM x as ones, x as tens, x as hundreds, x as thousand, x as tenthousands
)
  
--Create a list of dates starting with the first date in the leave table. 
, dates as (
  SELECT 
    DATEADD(day, y.row_num, DATEADD(day, -1, (SELECT MIN(LeaveDate) FROM emp_leave)) ) as [date]
  FROM y
)
  
--From the list of dates, add the weeday information.
, days as (
  SELECT 
    d.*
    , DATEPART(weekday, d.[date]) as weekday_num
    , FORMAT(d.[date], 'dddd') as weekday_name
  FROM [dates] as d
)

--Limit the list of dates(with weekday info) to just those matching the workdays for the employee
, leave_dates as (
  SELECT 
    --TOP (100) --Use for testing.
    ew.ID
    , el.Employee
    , el.LeaveDate
    , el.Duration
    , d.[date]
    , d.weekday_num
    , d.weekday_name
    , ROW_NUMBER() OVER(ORDER BY d.[date]) as row_num
  FROM days as d
    INNER JOIN emp_workdays as ew
      ON ew.WorkingDayNumber = d.weekday_num
    INNER JOIN emp_leave as el
      ON el.ID = ew.ID
      --AND el.LeaveDate = d.[date]
      AND el.ID = 1  --Limit to only one employee ID.
)
  SELECT
    ID
    , Employee
    , [date] as [Leave Date]
    , weekday_name as [Day]
    , weekday_num as [Working Days]
  FROM leave_dates
  WHERE row_num <= Duration

| 识别号|雇员|休假日期|日|工作日|
| - ------|- ------|- ------|- ------|- ------|
| 1个|一百零一|二〇二二年十二月三十日|星期五|六个|
| 1个|一百零一|二〇二二年十二月三十一日|星期六|七|
| 1个|一百零一|2023年1月1日|星期日|1个|
| 1个|一百零一|2023年1月6日|星期五|六个|
| 1个|一百零一|2023年1月7日|星期六|七|
fiddle