联接到联接项具有多行的表

如果你有 PRIMARY KEY 在 user_actions :

SELECT  u.*, ua.*
FROM    users u
LEFT JOIN
        user_actions ua
ON      user_actions.id = 
        (
        SELECT  TOP 1 id
        FROM    user_actions uai
        WHERE   uai.userid = u.userid
                AND uai.action_date >= u.pay_date
        ORDER BY
                uai.action_date
        )

如果你不这样做:

WITH    j AS
        (
        SELECT  u.*, ua.*, ROW_NUMBER() OVER (PARTITION BY ua.userid ORDER BY ua.action_date) AS rn, ua.action_date
        FROM    users u
        LEFT JOIN
                user_actions ua
        ON      ua.userid = u.userid
                AND ua.action_date >= u.pay_date
        )
SELECT  *
FROM    j
WHERE   rn = 1 or action_date is null

更新:

CROSS APPLY 提出的 @AlexKuznetsov 更优雅更高效。

交叉申请是您的朋友:

select users.*, t.* from users
CROSS APPLY(SELECT TOP 1 * FROM user_actions WHERE user_actions.userid = users.userid
AND user_actions.action_date >= users.pay_date
order by user_actions.pay_date) AS t

select u.*, ua.* from
    users u join users_actions ua on u.userid = ua.userid
where
    ua.action_date in
            (select min(action_date) from user_actions ua1
             where
                  ua1.action_date >= u.pay_date and
                  u.userid=ua1.userid)