查找同时具有已验证用户和未验证用户的组

Question

(不是我真正的MySQL模式，但说明了需要做什么。)

用户可以属于多个组，并且组具有多个用户。

users:
  id INT
  validated TINYINT(1)

groups:
  id INT
  name VARCHAR(20)

groups_users:
  group_id INT
  user_id INT

我需要找到既包含已验证用户又包含未验证用户的组(validated分别为1或0 )，以便执行特定的手动维护任务。有数千个用户，每个用户至少属于一个组，但一个组通常只有2-5个用户。

这是一个实时生产服务器，所以我可能会自己创建一个查询，但我尝试的最后一个查询花了几分钟时间才杀死它。(我不是那些优秀的SQL向导中的一个。)我想我可以关闭服务器进行维护，但是，如果可能的话，在几秒钟内完成这项工作的查询将是非常棒的。

谢谢!

Answer 1

对于每个用户，选择与其在同一组中的所有用户。然后只选择验证不同的用户。

SELECT users u1 LEFT JOIN groups_users g1 ON id=user_id LEFT JOIN groups_users g2 ON group_id=group_id LEFT JOIN users u2 ON user_id=id WHERE u1.validated=1 AND u2.validated=0;

Answer 2

下面是一个完整的示例(我是在Oracle中完成的，但是主SQL查询应该可以在MySql上正常工作)。

CREATE TABLE USERS (ID INTEGER, validated NUMBER(1));
CREATE TABLE GROUPS (ID integer, NAME VARCHAR2(20));
CREATE TABLE groups_users (group_id integer, user_id INTEGER);

INSERT INTO USERS VALUES(1,0);
INSERT INTO USERS VALUES(1,1);
INSERT INTO USERS VALUES(2,0);
INSERT INTO USERS VALUES(3,0);
INSERT INTO USERS VALUES(3,1);

INSERT INTO GROUPS VALUES(1,'g1');
INSERT INTO GROUPS VALUES(2,'g2');
INSERT INTO GROUPS VALUES(3,'g3');

INSERT INTO groups_users VALUES (1,1);
INSERT INTO groups_users VALUES (2,2);
INSERT INTO groups_users VALUES (3,3);

 SELECT
DISTINCT g.id
    FROM groups g
   WHERE
          EXISTS (SELECT *
                   FROM GROUPS_USERS gu
                      , USERS u
                  WHERE gu.user_id = u.id
                    AND gu.group_id = g.id
                    AND u.validated = 0)
    AND   EXISTS (SELECT *
                   FROM GROUPS_USERS gu
                      , USERS u
                  WHERE gu.user_id = u.id
                    AND gu.group_id = g.id
                    AND u.validated = 1)

结果(组1和组3具有已验证和未验证的用户)：

1, 3