SQL Server按EVENTID顺序匹配关联两张表的SQL实现方法

你可以通过窗口函数ROW_NUMBER()分别对两张表的同组事件按EVENTID排序生成序号，再通过序号和子串匹配关联即可，具体SQL如下：

WITH RankedA AS (
    SELECT 
        EVENTID,
        CID,
        QID,
        -- 同QID下按EVENTID升序生成序号，最小EVENTID序号为1，以此类推
        ROW_NUMBER() OVER (PARTITION BY QID ORDER BY EVENTID ASC) AS rn
    FROM 表A
),
RankedB AS (
    SELECT 
        EVENTID AS B_EVENTID,
        CID AS B_CID,
        IRR,
        -- 同IRR下按EVENTID升序生成序号
        ROW_NUMBER() OVER (PARTITION BY IRR ORDER BY EVENTID ASC) AS rn
    FROM 表B
)
SELECT 
    a.EVENTID,
    a.CID,
    a.QID,
    b.B_EVENTID,
    b.B_CID,
    b.IRR
FROM RankedA a
INNER JOIN RankedB b 
    ON CHARINDEX(a.QID, b.IRR) > 0 -- 匹配QID为IRR子串的关联关系
    AND a.rn = b.rn -- 相同序号的事件匹配，即最小配最小、中间配中间、最大配最大
ORDER BY a.EVENTID ASC;

逻辑说明 #

• 先用CTE分别给表A、表B的同维度（表A按QID分组、表B按IRR分组）数据按EVENTID从小到大生成连续序号，每组的最小EVENTID序号为1，次之为2，最大为N
• 关联时同时满足两个条件：QID是IRR的子串、两个表的序号相等，即可实现要求的对位匹配效果
• 该方案不仅适配当前3条数据的场景，同组下任意数量的对应事件都可以按顺序匹配

内容的提问来源于stack exchange，提问作者Luke Darius