多对多关系下，如何SQL查询满足多个数据条件的用户？

user表 #

data表 #

约束：UNIQUE(data_kind, data_value)

user_data表 #

约束：UNIQUE(user_id, data_id)，索引：INDEX(data_id)

要查询同时拥有(data_kind=123,data_value='Hello')和(data_kind=456,data_value='World')的用户，以下是几种实用的SQL写法：

方法1：多次关联查询 #

通过两次关联user_data和data表，直接筛选出同时满足两个条件的用户：

SELECT u.user_id, u.user_name
FROM user u
JOIN user_data ud1 ON u.user_id = ud1.user_id
JOIN data d1 ON ud1.data_id = d1.data_id
JOIN user_data ud2 ON u.user_id = ud2.user_id
JOIN data d2 ON ud2.data_id = d2.data_id
WHERE d1.data_kind = 123 AND d1.data_value = 'Hello'
  AND d2.data_kind = 456 AND d2.data_value = 'World';

方法2：分组统计筛选 #

先筛选出符合两个条件的记录，关联用户后按用户分组，统计满足条件的条目数等于2的用户：

SELECT u.user_id, u.user_name
FROM user u
JOIN user_data ud ON u.user_id = ud.user_id
JOIN data d ON ud.data_id = d.data_id
WHERE (d.data_kind = 123 AND d.data_value = 'Hello')
   OR (d.data_kind = 456 AND d.data_value = 'World')
GROUP BY u.user_id, u.user_name
HAVING COUNT(DISTINCT d.data_id) = 2;

注：因为data表有UNIQUE(data_kind, data_value)约束，每个条件对应唯一data_id，用COUNT(*)也可，但COUNT(DISTINCT)更严谨，避免重复数据干扰结果。

方法3：EXISTS子查询 #

通过两次EXISTS判断用户是否同时拥有两个目标数据，逻辑直观且索引利用率高：

SELECT u.user_id, u.user_name
FROM user u
WHERE EXISTS (
    SELECT 1
    FROM user_data ud
    JOIN data d ON ud.data_id = d.data_id
    WHERE ud.user_id = u.user_id
      AND d.data_kind = 123 AND d.data_value = 'Hello'
)
AND EXISTS (
    SELECT 1
    FROM user_data ud
    JOIN data d ON ud.data_id = d.data_id
    WHERE ud.user_id = u.user_id
      AND d.data_kind = 456 AND d.data_value = 'World'
);

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