星辰
发布于

联接 2 个日期最近的表

我有 2 个表格,看起来像这样:

T1 航站楼

  
| Ordernr  | Itemnr   | Price    | Date         | Currency |
| -------- | -------- | -------- | ------------ | -------- |
| 1        | aaa      | 10       | 2024-04-15   | EUR      |
| 1        | bbb      | 20       | 2024-04-15   | EUR      |
| 1        | ccc      | 15       | 2024-04-15   | EUR      |
| 2        | aaa      | 20       | 2024-04-16   | USD      |
| 2        | bbb      | 20       | 2024-04-16   | USD      |
| 3        | aaa      | 20       | 2024-04-16   | EUR      |
| 3        | bbb      | 20       | 2024-04-16   | EUR      |

T2 航站楼

  
| Currency | Date         | ExchangeRate
| -------- | ------------ | ------------
| EUR      | 2024-04-17   | 10,4
| USD      | 2024-04-17   | 11,0
| EUR      | 2024-04-16   | 10,3
| USD      | 2024-04-16   | 11,1
| EUR      | 2024-04-14   | 10,2
| USD      | 2024-04-14   | 11,2

正如您在 t2 中看到的,在本例中缺少一些日期。2024-04-15

在我的查询中,我想从 t1 获得完整的订单价格,并将其乘以与 t2 中的日期和货币匹配的汇率,如果 t2 中缺少日期,我想匹配最近的日期。

这是我到目前为止得到的:

  Select 
 t1.Ordernr, 
 t1.Date,
 Sum(t1.Price * t2.ExchangeRate) as Orderprice,
 t2.ExchangeRate

from t1 
inner join t2
  on t1.Currency = t2.Currency
  and t1.Date = t2.Date

Group by t1.Date, t1.Ordernr, t1.Price, t2.ExchangeRate

这将产生以下结果:

  
| Ordernr  | Date         | Orderprice   | ExchangeRate
| -------- | ------------ | ------------ | ------------
| 1        | 2024-04-15   | Null         | Null
| 2        | 2024-04-16   | 444          | 11,1
| 3        | 2024-04-16   | 412          | 10,3

但我想要这个结果:

  
| Ordernr  | Date         | Orderprice   | ExchangeRate
| -------- | ------------ | ------------ | ------------
| 1        | 2024-04-15   | 459          | 10,2
| 2        | 2024-04-16   | 444          | 11,1
| 3        | 2024-04-16   | 412          | 10,3

我明白为什么我会得到 null 值,但我不知道如何获得我想要的结果。

浏览 (23)
点赞
收藏
1条评论
Klustron小助手
试试这个 SELECT t1.Ordernr, t1.Date, SUM(t1.Price * t2.ExchangeRate) as Orderprice, t2.ExchangeRate FROM t1 INNER JOIN t2 ON t1.Currency = t2.Currency AND t2.Date = ( SELECT subt2.Date FROM t2 AS subt2 WHERE subt2.Date >= t1.Date ORDER BY subt2.Date ASC LIMIT 1 ) GROUP BY t1.Date, t1.Ordernr, t1.Price, t2.ExchangeRate
点赞
评论