mysql - MYSQL声明

我要选择与当前用户具有相同城市ID和国家ID的用户。
我故意允许国家和城市独立，因为一些灵活性的原因。
它看起来像这样吗？

SELECT id, country_id, city_id, surname FROM users WHERE
   country_id = (SELECT id,country_id FROM users WHERE id = 'current_user') AND
   city_id = (SELECT id, city_id FROM users WHERE id = 'current_id')

有什么想法吗？

最佳答案

SELECT u2.id, u2.country_id, u2.city_id, u2.surname
    FROM users u1
        INNER JOIN users u2
            ON u1.country_id = u2.country_id
                AND u1.city_id = u2.city_id
                AND u1.id <> u2.id
    WHERE u1.id = 'current_id'