代码之家 › 专栏 › 技术社区 › not_ur_avg_cookie

在日期不同的日期加入表格

period join date sql-server sql

2

not_ur_avg_cookie · 技术社区 · 7 年前

所以基本上我想知道,如果日期不一样,我怎么能把两张表连起来。第一张桌子是我的主要桌子,包括我所有的顾客都买过东西。购买日期是过去的单点:

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 |
+--------+----------+-------+------------+----+-----------+----------+

谢谢你的帮助!!!!!!!!

3 回复 | 直到 7 年前

1

3

Cato 7 年前

您可以使用“外部应用”来选择报告日期之前存在的前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

2

forpas 7 年前

您可以这样加入:

select c.*, d.*
from customers c inner join discounts d
on d.startdate = (select max(startdate) from discounts where startdate <= c.reportdate)

见 demo

3

2

John Cappelletti 7 年前

只是另一个选择

;with cte as (
Select A.* 
      ,B.*
      ,RN = row_number() over (Partition by custid,reportdate order by startdate desc)
 From Customers A
 Join Discounts B on B.startdate<=A.ReportDate
) 
Select * 
 From  cte 
 Where RN=1

dbFiddle