无存储过程/函数下，验证预订收费与FEES表生效日期费用匹配的查询方法

刚好碰到过类似的场景，我来分享一个不需要存储过程或函数的解决方案，核心逻辑是为每个预订匹配到对应BookingTypeId下最新生效且不晚于预订日期的费用标准，再对比实际收取的费用是否一致。

核心查询代码（支持CTE的数据库：SQL Server、PostgreSQL、MySQL 8.0+等） #

WITH EffectiveFees AS (
    SELECT 
        BookingTypeId,
        FeeAmount,
        EffectiveFrom,
        -- 按生效日期倒序排名，同类型下最新生效的费用排第1
        ROW_NUMBER() OVER (
            PARTITION BY BookingTypeId 
            ORDER BY EffectiveFrom DESC
        ) AS FeeRank
    FROM FEES
)
SELECT 
    b.Date AS 预订日期,
    b.CustomerId AS 客户ID,
    b.BookingTypeId AS 预订类型ID,
    b.FeeCharged AS 实际收取费用,
    ef.FeeAmount AS 正确费用标准,
    -- 直观标记费用是否匹配
    CASE 
        WHEN b.FeeCharged = ef.FeeAmount THEN '✅ 费用匹配'
        WHEN ef.FeeAmount IS NULL THEN '⚠️ 无对应有效费用标准'
        ELSE '❌ 费用不匹配'
    END AS 费用验证状态
FROM BOOKINGS b
LEFT JOIN EffectiveFees ef 
    ON b.BookingTypeId = ef.BookingTypeId
    AND ef.EffectiveFrom <= b.Date
    AND ef.FeeRank = 1
-- 若只想查看异常记录，取消下面的注释
-- WHERE b.FeeCharged != ef.FeeAmount OR ef.FeeAmount IS NULL

针对不支持CTE的老版本数据库（比如MySQL 5.x） #

可以把CTE替换成子查询，逻辑完全一致：

SELECT 
    b.Date AS 预订日期,
    b.CustomerId AS 客户ID,
    b.BookingTypeId AS 预订类型ID,
    b.FeeCharged AS 实际收取费用,
    ef.FeeAmount AS 正确费用标准,
    CASE 
        WHEN b.FeeCharged = ef.FeeAmount THEN '✅ 费用匹配'
        WHEN ef.FeeAmount IS NULL THEN '⚠️ 无对应有效费用标准'
        ELSE '❌ 费用不匹配'
    END AS 费用验证状态
FROM BOOKINGS b
LEFT JOIN (
    SELECT 
        BookingTypeId,
        FeeAmount,
        EffectiveFrom,
        ROW_NUMBER() OVER (
            PARTITION BY BookingTypeId 
            ORDER BY EffectiveFrom DESC
        ) AS FeeRank
    FROM FEES
) ef 
    ON b.BookingTypeId = ef.BookingTypeId
    AND ef.EffectiveFrom <= b.Date
    AND ef.FeeRank = 1
-- WHERE b.FeeCharged != ef.FeeAmount OR ef.FeeAmount IS NULL

关键逻辑解释 #

1. EffectiveFees子查询/CTE：

   • 用PARTITION BY BookingTypeId把费用按预订类型分组
   • 用ORDER BY EffectiveFrom DESC让同类型下最新生效的费用排在最前面
   • ROW_NUMBER()给每组的费用记录排名，最新生效的费用会得到FeeRank = 1

2. 关联查询：

   • 把预订表和有效费用表关联，确保只匹配同预订类型、生效日期不晚于预订日期的最新费用
   • 用LEFT JOIN是为了保留所有预订记录，哪怕找不到对应有效费用（这种情况属于异常，需要排查）

3. 额外注意事项：

   • 如果你的数据库中money类型存在精度差异（比如不同字段保留小数位数不同），可以用ROUND函数统一精度后再对比，比如：

     CASE 
         WHEN ROUND(b.FeeCharged, 2) = ROUND(ef.FeeAmount, 2) THEN '✅ 费用匹配'
         -- ... 其他条件
     END AS 费用验证状态
     

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