您的错误是，其中oc.client_id = oca.client_id 应该是哪里oc.email = oca.email 。

您没有说您是哪个DBMS重新使用，但如果使用的是MS SQL，则以下操作也可以满足您的要求。

You didn't say which DBMS you're using, but if it's MS SQL, the following will also do what you want.

SELECT email, firstname
FROM (
    SELECT 
        email, firstname, 
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY client_id DESC) AS intRow
    FROM onsite_clients_archive
    WHERE users_user_id IS NULL
) AS T
WHERE intRow = 1