B表中有很多A表同一商品编码但是不同日期的数据，根据A表商品编码和日期，去获取最靠近B表同一商品编码，且A表日期最靠近B表日期那一行价格。

Question

根据A表数据商品编码，日期去获取B表同一编码，且最新日期的其中一行数据。
匹配条件例子：A表.商品编码 = B表.商品编码 AND A表.日期 >= B表.日期其中最新日期的其中一行。
B表中有很多A表同一商品编码但是不同日期的数据，根据A表商品编码和日期，去获取最靠近B表同一商品编码，且A表日期最靠近B表日期那一行价格。

Answer 1

  是这样吗
WITH T1 AS (  -- 测试数据 
SELECT '20220101' AS DT,'A01' CODE , 1  AMT UNION ALL 
SELECT '20220102' AS DT,'A01' CODE , 2 AMT UNION ALL 
SELECT '20220103' AS DT,'A01' CODE , 3 AMT UNION ALL 
SELECT '20220104' AS DT,'A01' CODE , 4 AMT UNION ALL 
SELECT '20220105' AS DT,'A01' CODE , 5  AMT UNION ALL 
SELECT '20220106' AS DT,'A01' CODE , 6 AMT UNION ALL 
SELECT '20220111' AS DT,'A01' CODE , 11 AMT UNION ALL
SELECT '20220111' AS DT,'B01' CODE , 8 AMT    )  
, T2 AS (  -- 测试数据 
SELECT '20221031' AS DT,'A01' CODE   UNION ALL 
SELECT '20221001' AS DT,'B01' CODE   UNION ALL 
SELECT '20220901' AS DT,'B02' CODE   UNION ALL 
SELECT '20220902' AS DT,'B03' CODE   UNION ALL 
SELECT '20220903' AS DT,'B04' CODE   UNION ALL 
SELECT '20220904' AS DT,'B05' CODE   UNION ALL 
SELECT '20220905' AS DT,'B06' CODE   UNION ALL
SELECT '20220906' AS DT,'B07' CODE    )  
SELECT DISTINCT * 
FROM (
SELECT T1.CODE
            ,MAX(T1.DT)OVER(PARTITION BY T1.CODE ORDER BY CODE) DT 
            ,MAX(T1.AMT)OVER(PARTITION BY T1.CODE ORDER BY CODE) AMT
FROM T2,T1 
WHERE  T2.CODE = T1.CODE) T1