sql - Select the last to a certain date record from the dependent table

Question

In SQL Server I have 2 tables

table_a

card_number reissue_date
1           15.02.2017 00:00:00
2           01.02.2017 00:00:00
3           01.01.2017 00:00:00

table_b

card_number timestamp               limit
1           01.01.2017 00:00:00     9999
1           01.02.2017 00:00:00     100000
1           01.03.2017 00:00:00     25000
2           01.01.2017 00:00:00     10001
3           01.03.2017 00:00:00     5000
3           01.04.2017 00:00:00     0

Expected Result

card_number limit
1           100000
2           10001

I tried many options in this, I came close to a solution, but I could not display the column "limit", what is the best way to solve this problem?

My wrong decision

SELECT table_b.card_number, Max(timestamp)
FROM   table_b LEFT JOIN table_a
  ON ( table_b.card_number = table_a.card_number
   AND table_b.timestamp < table_a.reissue_date )
WHERE  table_a.reissue_date IS NOT NULL
GROUP  BY table_b.card_number;

It is necessary to make a selection of the latest by date records of the table_b table, but not larger than the date in table_a

The working solution I came to at the moment, I'm not sure if it works correctly, but with my initial data it gave the desired result

SELECT card_number, 
       Max(timestamp), 
       Max(limit) AS timestamp 
FROM   table_b 
WHERE  table_b.timestamp < (SELECT reissue_date 
                            FROM   table_a 
                            WHERE  card_number = table_b.card_number) 
GROUP  BY card_number;

标签： sqlsql-serverdatetimesubquerylateral-join

Answer 1

在 SQL Server 中，我们可以使用行限制横向连接将第二张表中的最新记录置于第一张表的时间戳之前：

select a.card_number, b.*
from table_a a
cross apply (
    select top (1) b.*
    from table_b b 
    where b.card_number = a.card_number and b.timestamp < a.reissue_date 
    order by b.timestamp desc
) b

这也消除了不匹配的行，这与您的数据一致。