求教一个sql查询相关语法

[狼异族]

CREATE TABLE T_A(ID int, Date int, Value_A int)
CREATE TABLE T_B(ID int, Date int, Value_B int)

INSERT INTO T_A
VALUES (1, 20200101, 1),
(1, 20200102, 2),
(1, 20200103, 5),
(2, 20200101, 5),
(2, 20200102, 3),
(2, 20200103, 4),
(3, 20200101, 1),
(3, 20200102, 2),
(3, 20200103, 3)

INSERT INTO T_B
VALUES (1, 20200101, 2),
(1, 20200102, 5),
(1, 20200103, 6),
(2, 20200101, 2),
(2, 20200102, 3),
(2, 20200103, 4),
(3, 20200101, 5),
(3, 20200102, 6),
(3, 20200103, 7)

SELECT a.ID, a.Date, a.Value_A, c.Value_B FROM T_A a
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date ASC) AS RowId FROM T_B ) b ON a.ID = b.ID AND a.Date = b.Date
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date ASC) AS RowId FROM T_B ) c ON b.ID = c.ID AND b.RowId = c.RowId+1

目的是想根据A表中的ID,Date，从B表中选出ID相同，小于A表Date的最大日期对应的Value_B， 问下有没有什么高效简洁的语法

[狼异族]

我自己给的语句有很严重的问题，就在B表中找不到A表中相同的日期就筛选不出来，而实际需求中大部分都是找不到的

[RINK_1]

SELECT A.* FROM T_B A
OUTER APPLY (SELECT TOP 1 * FROM T_A WHERE A.ID=ID ORDER BY DATE DESC) AS B
WHERE A.Date<B.Date

[Hello World,]

SELECT  *
FROM    t_a A
        OUTER APPLY(SELECT      TOP(1) b.Value_B
                    FROM        t_b b
                    WHERE       A.id = b.id AND A.[Date] > b.[Date]
                    ORDER BY    b.[Date] DESC)B;

[吉普赛的歌]

#3 的写法是对的， 不过在数据量大的情况下， 还是要建立索引来保证效率。