將每行的連接限制為前 1 行

我正在嘗試加入兩個表並過濾加入結果，但我無法管理它，我將不勝感激。

我有這些行集：

Table A
id | userid | targetid | start_date
-----------------------------------
11 | user1  | 123      | 22/10/2019
22 | user1  | 123      | 02/10/2019
33 | user1  | 123      | 04/10/2019
44 | user1  | 456      | 02/10/2019
55 | user1  | 123      | 13/11/2020

Table B
id | targetid | start_date
---------------------------
66 | 123      | 21/10/2019
77 | 456      | 11/11/2020
88 | 123      | 11/11/2020
99 | 123      | 12/11/2020

我想要做的是使用作為 FK 並使用作為過濾器/訂單值**的每個表 A 行查找最近的表 B 行。**這是我正在尋找的結果：targetid``start_date

11 | user1  | 123      | 66
22 | user1  | 123      | 66
33 | user1  | 123      | 66
44 | user1  | 456      | 77
55 | user1  | 123      | 99

我嘗試使用內部連接和限制連接條件，on ... and a.start_date <= b.start_date但這使得表 A | 55 與表 B 中的每一行連接。

這可以是任何類型的 SQL，加入/游標/循環方法和性能無關緊要。

SQL小提琴

所以有兩種方法可以做到這一點：

1. 如果您有保證組合(targetId,start_date)唯一的鍵，則相關子查詢將返回一致的結果
2. 如果不能保證唯一性，則必須使用分區函式以保證每次執行查詢時返回相同的結果

如果存在唯一索引：

SELECT
 a.id
,a.userid
,a.targetid
,b.id AS b_id
/* And other columns from Table B */
FROM
 table_a a
LEFT JOIN
 table_b b
   ON b.targetid = a.targetid
       AND b.start_date =
         (
           SELECT
             MAX(start_date)
           FROM
             table_b
           WHERE
             targetid = a.targetid
               AND start_date <= a.start_date
         )

不存在唯一索引：

為此，我假設IdonTable B是行標識符並且是唯一的。如果不是，您將需要在語句的SORT部分添加額外的列以保證查詢是確定性的。PARTITION

SELECT
 id
,userid
,targetid
,b_id
/* Other columns from table b */
FROM
 (
   SELECT
     a.id
    ,a.userid
    ,a.targetid
    ,b.id AS b_id
    /* Other columns from Table B */
    ,ROW_NUMBER()
       OVER
         (
           PARTITION BY
             a.id
           ORDER BY
             b.start_date DESC
            ,b.id DESC
         ) AS rownum
   FROM
     table_a a
   LEFT JOIN
     table_b b
       ON b.targetid = a.targetid
           AND b.start_date <= a.start_date
 ) x
WHERE
 rownum = 1

筆記：

正如我在之前的評論中提到的，您提供的預期結果與要求不一致tableb.start_date <= tablea.start_date，特別是Ids22、33、44。

WITH CTE AS (
SELECT a.id, a.userid, a.targetid, b.id AS b_id,
   ROW_NUMBER() OVER (PARTITION BY  a.id, a.userid, a.targetid ORDER BY b.start_date) AS rn      
FROM a
JOIN b ON a.targetid = b.targetid AND b.start_date >= a.start_date
)
SELECT  id, userid, targetid, b_id
FROM CTE WHERE rn = 1

引用自：https://dba.stackexchange.com/questions/279576