特定组中的用户但不包含在其他组中

[SvenC56 (Sven)]

在特定组中但不在其他组中的用户

信息：参数必须写成数组格式。例如：{1,2,3}

-- [params]  
-- string :opt_in_groups
-- string :opt_out_groups
SELECT
   u.id AS user_id,
   g.id AS GROUP_ID 
FROM
   users u 
   JOIN
      group_users gu 
      ON gu.user_id = u.id 
   JOIN
      GROUPS g 
      ON g.id = gu.group_id 
WHERE
   g.id = ANY (:opt_in_groups::int[]) 
   AND u.id NOT IN 
   (
      SELECT
         u.id AS user_id 
      FROM
         users u 
         JOIN
            group_users gu 
            ON gu.user_id = u.id 
         JOIN
            GROUPS g 
            ON g.id = gu.group_id 
      WHERE
         g.id = ANY (:opt_out_groups::int[]) 
   )
ORDER BY
   u.primary_group_id