我有两张桌子,[预订]和[食品价格].
表[保留]有两行:
id userId foodId date
1 1 1 2023-02-08
2 1 2 2023-03-14
[食品价格]表有四行:
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
我希望以这样的方式连接这两个表,使结果具有每个预订的最新价格.我写了这样一个查询:
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
结果. 我应该如何写这样的查询呢?