在SQL Server中跨多个行添加介于2个日期之间的时间

-- 创建一个示例表
CREATE TABLE ExampleTable (
    ID INT,
    StartDate DATE,
    EndDate DATE
);

-- 插入示例数据
INSERT INTO ExampleTable (ID, StartDate, EndDate)
VALUES (1, '2022-01-01', '2022-01-05'),
       (2, '2022-01-03', '2022-01-07'),
       (3, '2022-01-06', '2022-01-10');

-- 查询介于两个日期之间的时间
SELECT ID, StartDate, EndDate,
       CASE
           WHEN StartDate >= '2022-01-02' AND EndDate <= '2022-01-08' THEN DATEDIFF(DAY, StartDate, EndDate)
           WHEN StartDate >= '2022-01-02' AND EndDate > '2022-01-08' THEN DATEDIFF(DAY, StartDate, '2022-01-08')
           WHEN StartDate < '2022-01-02' AND EndDate <= '2022-01-08' THEN DATEDIFF(DAY, '2022-01-02', EndDate)
           ELSE DATEDIFF(DAY, '2022-01-02', '2022-01-08')
       END AS DaysBetween
FROM ExampleTable;