当值丢失时更新数据库表

Question

我有两个表：

Bill：

create table Bill(
               BillDate datetime,
               Amount decimal(10,2) ,
               BillCurrency varchar(3) ,
               ReportingCurrency decimal(10,2))

FxRate：

create table FxRate( 
               RateDate datetime,
               SourceCurrency varchar(3),
               TargetCurrency varchar(3),
               ExchangeRate decimal(15,4))

这就是我想要做的：

我想更新我的 Bill 表，因为

update Bill 
set ReportingCurrency = FxRate.ExchangeRate * Bill.Amount
from FxRate
where FxRate.RateDate = Bill.BillDate

在此更新所有包含以下条目的行：该特定日期将获得新的报告货币数据。 由于 Bill 表可以有多行符合更新条件，因此我遇到以下问题：

对于 FxRate 表（该日期）中没有条目的行，ReportingCurrency 变为 NULL。 我想返回最近的 <= RateDate 并获取汇率。是否可以使用同一更新语句中的修改或其他一些有效的方法？ （我想避免光标）。

Answer 1

您应该能够通过子查询来实现这一点。 希望我下面的示例无需修改即可运行（并且不会出现错误！）唯一的注意事项是您需要用您的报告（基础）货币替换我假设的“美元”。

UPDATE Bill SET ReportingCurrency = (Bill.Amount * 
  (SELECT TOP 1 FxRate.ExchangeRate FROM FxRate
   WHERE FxRate.SourceCurrency = Bill.BillCurrency
   AND FxRate.TargetCurrency = 'USD'
   AND FxRate.RateDate <= Bill.BillDate
   ORDER BY FxRate.RateDate DESC))

希望这可以帮助。 编辑 - 添加 ORDER BY 子句

Answer 2

当然这是可能的 - 作为 SELECT，这将是：

SELECT
  b.BillDate,
  r.RateDate,
  r.ExchangeRate
FROM
  Bill b
  LEFT JOIN FxRate r ON r.RateDate = (
    SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate
  )

因此，作为更新：

UPDATE
  Bill
SET
  ReportingCurrency = r.ExchangeRate * b.Amount
FROM
  Bill b
  LEFT JOIN FxRate r ON r.RateDate = (
    SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate
  )

解决方案做出以下假设：

• 对于任何给定的一天，永远不会有超过一个 FxRate 记录，
• 有一个 早于所有 Bill 记录的 FxRate 记录

如果这些假设不适用，则必须适应查询。

另请注意，这是 SQL Server 语法。 Sybase 可能有点不同。

---

由于评论者对一般的“最近日期”解决方案表现出兴趣：

UPDATE
  Bill
SET
  ReportingCurrency = r.ExchangeRate * b.Amount
FROM
  Bill b
  LEFT JOIN FxRate r ON r.RateDate = (
    SELECT TOP 1 RateDate 
    FROM         FxRate
    ORDER BY     ABS(DATEDIFF(d, RateDate, b.BillDate)), RateDate
  )

不过，这会很慢。 选择：

UPDATE
  Bill
SET
  ReportingCurrency = CASE 
                      WHEN DATEDIFF(d, r1.RateDate, b.BillDate) <= DATEDIFF(d, b.BillDate, r2.RateDate)
                      THEN r1.ExchangeRate
                      ELSE COALESCE(r2.ExchangeRate, r1.ExchangeRate)
                      END * b.Amount
FROM
  Bill b
  LEFT JOIN FxRate r1 ON r1.RateDate = (
    SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate
  )
  LEFT JOIN FxRate r2 ON r2.RateDate = (
    SELECT MIN(RateDate) FROM FxRate WHERE RateDate >= b.BillDate
  )