MS SQL：如何在多个用户的另一个日期（付款日期）之前获取最新日期（billdate）？

Question

示例表如下：

表1

userid | billdate  
user1 | 2017-06-01 
user1 | 2017-05-01 
user1 | 2017-04-01 
user2 | 2017-06-01 
user2 | 2017-05-01 

表2

userid | paymentdate 
user1 | 2017-06-01 
user1 | 2017-06-02 
user1 | 2017-05-02 
user2 | 2017-06-30 
user2 | 2017-05-30 

希望的输出提前

userid | billdate  | paymentdate 
user1 | 2017-05-01 | 2017-06-01 
user1 | 2017-06-01 | 2017-06-02 
user1 | 2017-05-01 | 2017-05-02 
user2 | 2017-06-01 | 2017-06-30 
user2 | 2017-05-01 | 2017-05-30 

由于

增加了一个场景：如果同一月份有多个付款，该怎么办？并且对于每个付款日期，先前的帐单日期应该在期望的输出中

Answer 1

如果你只想返回那里是一个Bill和Payment记录的记录，那么我们首先需要作出一些假设 - 即不会有任何两个Bill付款之间有两个Payment记录，或在同一天支付两笔Bill付款。

假设这一切，你可以使用cross apply获得最新Payment记录每个Bill记录，不返回任何东西在没有Payment记录可供选择：

declare @Bills table(userid nvarchar(10),billdate date); 
insert into @Bills values ('user1','2017-06-01'),('user1','2017-05-01'),('user1','2017-04-01'),('user2','2017-06-01'),('user2','2017-05-01'); 

declare @Payments table(userid nvarchar(10),paymentdate date); 
insert into @Payments values ('user1','2017-06-01'),('user1','2017-06-02'),('user1','2017-05-02'),('user2','2017-06-30'),('user2','2017-05-30'); 

select p.userid 
     ,ba.billdate 
     ,p.paymentdate 
from @Payments p 
    cross apply(select top 1 billdate 
       from @Bills b 
       where p.userid = b.userid 
        and b.billdate < p.paymentdate 
       order by b.billdate desc 
       ) ba 
order by p.userid 
     ,ba.billdate desc 
     ,p.paymentdate desc; 

输出：

+--------+------------+-------------+ 
| userid | billdate | paymentdate | 
+--------+------------+-------------+ 
| user1 | 2017-06-01 | 2017-06-02 | 
| user1 | 2017-05-01 | 2017-06-01 | 
| user1 | 2017-05-01 | 2017-05-02 | 
| user2 | 2017-06-01 | 2017-06-30 | 
| user2 | 2017-05-01 | 2017-05-30 | 
+--------+------------+-------------+ 

Answer 2

您似乎想要加入表格，但没有密钥。 row_number()可以提供关键：

select t1.*, t2.paymentdate 
from (select t1.*, 
      row_number() over (partition by userid order by billdate) as seqnum 
     from t1 
    ) t1 join 
    (select t2.*, 
      row_number() over (partition by userid order by paymentdate) as seqnum 
     from t2 
    ) t2 
    on t1.userid = t2.userid and t1.seqnum = t2.seqnum; 

Answer 3

你需要从Table01所有行，然后得到的只有一行从TABLE02升序排列。 Table02行通过用户标识和日期连接（billdate < = paymentdate）。顶部（1）的外部应用将会诀窍。

drop table if exists dbo.Table01; 
drop table if exists dbo.Table02; 

create table dbo.Table01 (
    userid varchar(100) 
    , billdate date 
); 

create table dbo.Table02 (
    userid varchar(100) 
    , paymentdate date 
); 

insert into dbo.Table01 (userid, billdate) 
values ('user1', '2017-06-01'), ('user1', '2017-05-01') 
    , ('user2', '2017-06-01'), ('user2', '2017-05-01'); 

insert into dbo.Table02 (userid, paymentdate) 
values ('user1', '2017-06-02'), ('user1', '2017-05-02') 
    , ('user2', '2017-06-30'), ('user2', '2017-05-30'); 


select 
* 
from dbo.Table01 t1 
    outer apply (
      select 
       top (1) 
       t2.paymentdate 
      from dbo.Table02 t2 
      where t1.userid = t2.userid 
       and t1.billdate <= t2.paymentdate 
      order by t2.paymentdate asc 
    ) tt 

Answer 4

有两种可能的解决方案：

首先

合并表为一体。这将使哪些日期属于哪个账单和哪个用户更清楚。因此，所需的输出是您正在使用的表格。如果用户已被记帐但尚未付款。付款日期是NULL。

二

添加主键到table 1和表2中使用它作为外键，像这样：

bill_id | userid |s billdate 1 | user1 |s 2017-06-01 2 | user1 |s 2017-05-01 3 | user2 |s 2017-06-01 4 | user2 |s 2017-05-01

表2

bill_id | userid |s paymentdate 1 | user1 |s 2017-06-02 2 | user1 |s 2017-05-02 3 | user2 |s 2017-06-30 4 | user2 |s 2017-05-30

现在你可以轻松获得连接：

SELECT table1.*, table2.paymentdate FROM table1 LEFT JOIN table2 ON table1.bill_id = table2.bill_id;