sql - 在日期不同时加入日期表

Question

所以基本上我想知道如何在日期不同的情况下连接两个表。第一张表是我的主表，其中包括我所有已购买商品的客户。购买日期为过去单点:

Customers
    +--------+----------+-------+------------+
    | custid | Quantity | Price | ReportDate |
    +--------+----------+-------+------------+
    | 371965 |       12 |     2 | 9/1/2016   |
    | 371965 |        2 |     5 | 2/25/2018  |
    | 377958 |       45 |     3 | 9/1/2016   |
    | 270723 |       12 |  1.25 | 5/1/2014   |
    | 270723 |    10.86 |  1.25 | 6/1/2014   |
    | 270723 |    12.29 |   1.3 | 7/1/2014   |
    | 270723 |    12.29 |   1.4 | 9/15/2016  |
    +--------+----------+-------+------------+

所以我想加入我的客户表并享受折扣。折扣生效的时间基本上是直到发布新折扣的整个时间段:

Discounts
    +----+-----------+----------+
    | id | startdate | discount |
    +----+-----------+----------+
    |  1 | 7/18/2013 |      0.1 |
    |  2 | 1/10/2014 |     0.25 |
    |  3 | 7/11/2016 |     0.11 |
    |  4 | 9/14/2016 |     0.12 |
    |  5 | 1/12/2017 |     0.15 |
    |  6 | 2/6/2017  |     0.22 |
    |  7 | 6/28/2017 |     0.09 |
    +----+-----------+----------+

所以我的目标是链接两个表并查看哪个购买日期落在适当的折扣区间内。这将是我的目标:

+--------+----------+-------+------------+----+-----------+----------+
| custid | Quantity | Price | ReportDate | id | startdate | discount |
+--------+----------+-------+------------+----+-----------+----------+
| 371965 |       12 |     2 | 9/1/2016   |  3 | 7/11/2016 |     0.11 |
| 371965 |        2 |     5 | 2/25/2018  |  7 | 6/28/2017 |     0.09 |
| 377958 |       45 |     3 | 9/1/2016   |  3 | 7/11/2016 |     0.11 |
| 270723 |       12 |  1.25 | 5/1/2014   |  2 | 41649     |     0.25 |
| 270723 |    10.86 |  1.25 | 6/1/2014   |  2 | 1/10/2014 |     0.25 |
| 270723 |    12.29 |   1.3 | 7/1/2014   |  2 | 1/10/2014 |     0.25 |
| 270723 |    12.29 |   1.4 | 9/15/2016  |  4 | 9/14/2016 |     0.12 |
+--------+----------+-------+------------+----+-----------+----------+

Answer 1

您可以使用 OUTER APPLY 选择报告日期之前存在的 TOP 1 折扣，按日期降序排列以获得最近的操作

  SELECT C1.*,DQ.*  FROM CUSTOMERS C1 OUTER APPLY 
                 (SELECT TOP 1 D.* FROM Discounts D WHERE C1.ReportDate >= D.startDate  
                                       ORDER BY D.StartDate DESC) DQ