如何编写SQL查询关联带起止日期的Account与VATRate表？

现有表结构及数据 #

DECLARE @Account TABLE
(   
    [AccountID] INT
    ,[Name] CHAR(10)
    ,[StartDate] DATE
    ,[EndDate] DATE
    ,[VATID] INT
);

DECLARE @VATRate TABLE
(   
    [VATID] INT
    ,[StartDate] DATE
    ,[EndDate] DATE
    ,[Rate] NUMERIC(4,1)
);

INSERT INTO @Account ([AccountID], [Name], [StartDate], [EndDate], [VATID])
VALUES (1, 'Account A', '2023-01-01', '2024-12-31', 1)
      ,(2, 'Account B', '2022-06-01', NULL, 2);
INSERT INTO @VATRate ([VATID], [StartDate], [EndDate], [Rate])
VALUES (1, '2022-01-01', '2023-12-31', 7.7)
      ,(1, '2024-01-01', NULL, 8.1)
      ,(2, '2020-01-01', '2022-12-31', 3.5)
      ,(2, '2023-01-01', NULL, 4.2);

注：修正了原表定义中的数据类型问题——CHAR长度不足会截断名称，INT无法存储带小数的税率；同时统一日期格式为SQL标准的YYYY-MM-DD避免歧义。

需求结果 #

需要得到以下格式的查询结果：

解决方案 #

你之前尝试CROSS APPLY未成功，核心原因是没正确处理期间重叠的判断逻辑，尤其是NULL代表“当前有效”的场景。以下是两种可行的实现方式：

方式一：使用JOIN关联 #

SELECT
    a.AccountID,
    a.Name,
    a.StartDate AS A_StartDate,
    a.EndDate AS A_EndDate,
    vr.Rate,
    vr.StartDate AS T_StartDate,
    vr.EndDate AS T_EndDate
FROM @Account a
JOIN @VATRate vr ON a.VATID = vr.VATID
-- 核心：判断账户期间与税率期间存在重叠
WHERE 
    (a.StartDate <= ISNULL(vr.EndDate, GETDATE()))
    AND (ISNULL(a.EndDate, GETDATE()) >= vr.StartDate);

方式二：使用CROSS APPLY #

SELECT
    a.AccountID,
    a.Name,
    a.StartDate AS A_StartDate,
    a.EndDate AS A_EndDate,
    vr.Rate,
    vr.StartDate AS T_StartDate,
    vr.EndDate AS T_EndDate
FROM @Account a
CROSS APPLY (
    SELECT * 
    FROM @VATRate vr
    WHERE vr.VATID = a.VATID
        AND (a.StartDate <= ISNULL(vr.EndDate, GETDATE()))
        AND (ISNULL(a.EndDate, GETDATE()) >= vr.StartDate)
) vr;

逻辑说明 #

1. 先通过VATID关联两张表，确保是同一增值税主体的记录
2. 期间重叠判断规则：
   • 账户的开始日期不能晚于税率的结束日期（税率无结束日期时，用当前日期替代）
   • 账户的结束日期不能早于税率的开始日期（账户无结束日期时，用当前日期替代）
3. 用ISNULL处理EndDate为NULL的情况，代表该账户/税率当前仍在生效

内容的提问来源于stack exchange，提问作者MuadDib