SQL 选择最大值，在特定范围日期字段关联

Question

>我应该通过比较销售日期在发票表上选择购买成本 例

发票表（约20000行）

| num. | cod_art | date |
---------------------
|   1  |  OROK3  | 22/12/2013 |
|   1  |  PIZZ4  | 22/12/2013 |
|   1  |  MYGO5  | 22/12/2013 |
|   2  |  CAND4  | 27/12/2013 |
|   2  |  OROK3  | 27/12/2013 |
|   2  |  SAGA8  | 27/12/2013 |
|   3  |  PIZZ4  | 30/12/2013 |
|   4  |  CAND4  | 30/12/2013 |

每个项目的成本表（约 4000 行）

| cod_art |  cost  | purchase dt.|
---------------------
|  OROK3  | 11.23  |  15/12/2013 |
|  OROK3  | 10.55  |  17/12/2013 |
|  OROK3  | 12.00  |  24/12/2013 |
|  OROK3  | 11.50  |  30/12/2013 |
|  PIZZ4  |  2.56  |  10/12/2013 |
|  PIZZ4  |  3.00  |  24/12/2013 |
|  SAGA8  | 23.45  |  25/12/2013 |
|  CAND4  |  1.33  |  31/12/2013 |

预期成果

| num. | cod_art |    date    ||  cost  | purchase dt.|
---------------------
|   1  |  OROK3  | 22/12/2013 || 10.55  |  17/12/2013 |
|   1  |  PIZZ4  | 22/12/2013 ||  2.56  |  10/12/2013 |
|   1  |  MYGO5  | 22/12/2013 ||  null  |     null    |
|   2  |  CAND4  | 27/12/2013 ||  null  |     null    |
|   2  |  OROK3  | 27/12/2013 || 12.00  |  24/12/2013 |
|   2  |  SAGA8  | 27/12/2013 || 23.45  |  25/12/2013 |
|   3  |  PIZZ4  | 30/12/2013 ||  3.00  |  24/12/2013 |
|   4  |  CAND4  | 30/12/2013 ||  null  |     null    |

这是一个可能的解决方案吗？

    SELECT * 
    FROM (  SELECT 
                *,
                dense_rank() over(PARTITION BY idfat,art order by data_purch desc) rn 
            FROM (  SELECT  
                        i.idfat,i.art,i.data_sale,p.data_purch,p.PRZ 
                    FROM @Invoice i 
                        left join @Purchase p on i.art=p.art 
                    WHERE 
                        data_sale>data_purch
                ) t
        ) src 
    WHERE 
        rn = 1

什么是最佳解决方案？

Answer 1

如果商品在一天内没有购买超过一次，那么以下查询可能会帮助您获得结果。

SELECT I.idfat, I.art, I.Data_Sale, P.Data_Sale, p.PRZ 
FROM Invoice As I
LEFT JOIN Purchase As P On P.art = I.art
    AND P.data_purch =
        (SELECT TOP 1 PA.data_purch
         FROM Purchase As PA
         WHERE PA.art = I.art
           AND PA.data_purch <= I.Data_Sale
         ORDER BY PA.data_purch Desc
        )