如何编写SQL关联查询获取每条预订对应的最新食品价格？

表结构与数据 #

预订表 [reservation] #

id  userId  foodId  date
1     1      1     2023-02-08
2     1      2     2023-03-14

食品价格表 [food-prices] #

id  foodId    date      price
1     1    2023-01-24   25
2     1    2023-03-05   30
3     2    2023-01-24   25
4     2    2023-03-05   30

需求：关联两张表，得到每条预订对应的最新食品价格（即预订日期之前或当天的最近一次价格更新）。

原查询与问题 #

原SQL语句：

SELECT [reservation].[id]
                    ,[reservation].[userId]
                    ,[reservation].[foodId]
                    ,[reservation].[date]
                    ,[food-prices].date
                    ,[food-prices].price
FROM [food].[dbo].[reservation]
LEFT OUTER JOIN [food-prices]
ON [reservation].foodId = [food-prices].foodId
AND [reservation].date >= [food-prices].date

原查询返回结果：

id  userId  foodId  [reservation].[date]    [food-prices].date    price
1      1       1         2023-02-08              2023-01-24        25
2      1       2         2023-03-14              2023-01-24        25
2      1       2         2023-03-14              2023-03-05        30

期望结果：

id  userId  foodId  [reservation].[date]    [food-prices].date    price
1      1       1         2023-02-08              2023-01-24        25
2      1       2         2023-03-14              2023-03-05        30

解决方案 #

方法1：使用窗口函数（推荐） #

通过ROW_NUMBER()按预订记录分组，对符合条件的价格日期倒序排序，筛选出每组中排序为1的记录（即对应预订的最新价格）：

WITH valid_prices AS (
    SELECT 
        r.id AS reservation_id,
        r.userId,
        r.foodId,
        r.date AS reservation_date,
        fp.date AS price_date,
        fp.price,
        ROW_NUMBER() OVER (PARTITION BY r.id ORDER BY fp.date DESC) AS rn
    FROM [food].[dbo].[reservation] r
    LEFT JOIN [food-prices] fp 
        ON r.foodId = fp.foodId 
        AND fp.date <= r.date
)
SELECT 
    reservation_id AS id,
    userId,
    foodId,
    reservation_date,
    price_date,
    price
FROM valid_prices
WHERE rn = 1;

方法2：使用子查询获取最大价格日期 #

先为每条预订找到对应食品在预订日期前的最新价格日期，再关联价格表获取对应价格：

SELECT 
    r.id,
    r.userId,
    r.foodId,
    r.date AS reservation_date,
    fp.date AS price_date,
    fp.price
FROM [food].[dbo].[reservation] r
LEFT JOIN [food-prices] fp 
    ON r.foodId = fp.foodId 
    AND fp.date = (
        SELECT MAX(date) 
        FROM [food-prices] 
        WHERE foodId = r.foodId AND date <= r.date
    );

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