(targets.name = 'age' AND targets.name = 'gender')将始终返回false，targets.name不能同时有两个值，使用OR而不是AND：

SELECT DISTINCT groups.* 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id 
WHERE groups.user_id = 1 
AND (targets.name = 'age' OR targets.name = 'gender');

字符串
如果两个目标都应该在那里，我们可以使用GROUP BY和HAVING子句以及CASE子句来专门选择包含两个目标的组：

SELECT groups.id
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id 
WHERE groups.user_id = 1 
GROUP BY groups.id
HAVING COUNT(CASE WHEN targets.name = 'age' THEN 1 END) >= 1
       AND COUNT(CASE WHEN targets.name = 'gender' THEN 1 END) >= 1

型

要获取目标为age的所有组，请在target name上使用具有适当过滤器的连接查询

SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'age' 
WHERE groups.user_id = 1;

id|
--+
 1|
 4|

字符串
对目标gender使用类似的查询

SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'gender' 
WHERE groups.user_id = 1;

id|
--+
 1|
 2|
 3|

型
要获得具有两个目标的组，请使用前面查询的intersect，即两个查询中返回的group_id

SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'age' 
WHERE groups.user_id = 1 
intersect
SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'gender' 
WHERE groups.user_id = 1; 

id|
--+
 1|

型

要查找同时具有性别和年龄目标的组，您应该使用不同的方法，例如将EXISTS子句与子查询一起使用。

SELECT DISTINCT groups.* 
FROM groups 
WHERE groups.user_id = 1 
AND EXISTS (
    SELECT 1
    FROM groups_targets 
    INNER JOIN targets ON targets.id = groups_targets.target_id 
    WHERE groups_targets.group_id = groups.id 
    AND targets.name IN ('age', 'gender')
    GROUP BY groups_targets.group_id
    HAVING COUNT(DISTINCT targets.name) = 2
);

字符串
该查询应该给予您想要的结果，即查找同时具有性别和年龄目标的组。
希望这对你有帮助。