mysql - 连接两个具有日期范围的表

Question

我有两个包含开始日期和结束日期的 SQL 表。

 Table 1: Name, AddedDate
 Table 2: Name, RemovedDate

我希望连接这两个表并将数据转储到一个临时表中，以显示何时从列表中添加和删除名称。

The same name may have been added and removed multiple times.

期望的输出示例

- Name, AddedDate, RemovedDate
- Jane, 2017-02-01, 2017-02-03
- Bill, 2017-01-28, (blank)
- Mike, 2017-01-15, 2017-01-19
- Jane, 2017-01-13, 2017-01-14

有人可以帮忙吗？谢谢。

Answer 1

另一个选项是OUTER APPLY(如果是 SQL Server)

示例

Declare @Table1 table (Name varchar(25),AddedDate date)
Insert Into @Table1 Values
('Jane', '2017-02-01'),
('Bill', '2017-01-28'),
('Mike', '2017-01-15'),
('Jane', '2017-01-13')

Declare @Table2 table (Name varchar(25),RemovedDate date)
Insert Into @Table2 Values
('Jane', '2017-02-03'),
('Mike', '2017-01-19'),
('Jane', '2017-01-14')


Select A.Name
      ,A.AddedDate
      ,B.RemovedDate
 From  @Table1 A
 Outer Apply (
                Select RemovedDate=min(RemovedDate)
                 From  @Table2
                 Where Name=A.Name
                  and  RemovedDate>=A.AddedDate
             ) B

返回

Name    AddedDate   RemovedDate
Jane    2017-02-01  2017-02-03
Bill    2017-01-28  NULL
Mike    2017-01-15  2017-01-19
Jane    2017-01-13  2017-01-14