查找组,两者都验证的,未经验证的用户
问题描述:
(不是我真正的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向导之一)。我想我可以将服务器关闭以进行维护,但是,如果可能的话,在几秒钟内完成此任务的查询将非常棒。
谢谢!
答
对于每个用户,选择与他在同一组中的所有用户。然后只选择验证不同的用户。
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;
答
下面是一个完整的例子(我做到了在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