我在我的数据库中有一个用于构建组的表。该表包含组名称和 ID 的列表。我有另一个包含用户的表，以及显示关系的第三个表。 (用户 ID、组 ID)。

情况是这样的，我需要创建属于特定组子集的用户标识列表。因此，例如，我想要组 1、3 和 8 中的所有用户。这很简单。但它变得更加复杂，我可能需要一个包含第 1、3 和 8 组或 1、2 和 8 组中的所有用户的列表。然后我可能需要排除符合该条件但也在组中的用户27.

所以我有一个脚本动态创建查询，使用在一定程度上起作用的子查询。我有两个问题。我认为我没有正确处理非部分，因为按照我的广告标准，最终它只是有点挂起。 (我认为这是我使用子选择而不是连接的结果，但我无法弄清楚如何使用连接来构建它。)

这是一个包含 4 个 ANDed OR 组和 2 个 NOT 子句的查询示例。

如果有更好的方法来优化这个 stmt，请告诉我。 (我可以用 PHP 处理它的动态构建)

如果我需要澄清任何事情或提供更多细节，请告诉我。

select * from users_table where username IN
(
    select user_id from
    (
        select distinct user_id from group_user_map where user_id in 
        (
            select user_id from 
            (
                select * from 
                (
                    select count(*) as counter, user_id from  
                    (
                        (
                            select distinct(user_id) from group_user_map where group_id in (2601,119)
                        ) 
                        union all
                        (
                            select distinct(user_id) from group_user_map where group_id in (58,226)
                        ) 
                        union all
                        (
                            select distinct(user_id) from group_user_map where group_id in (1299,525)
                        ) 
                        union all
                        (
                            select distinct(user_id) from group_user_map where group_id in (2524,128)
                        ) 
                    ) 
                    thegroups group by user_id
                ) 
                getall where counter = 4
            ) 
            getuserids
        ) 
        and user_id not in 
        (
            select user_id from group_user_map where group_id in (2572)
        ) 
    ) 
    biggergroup 
);

请注意，查询的第一部分是将 ID 与用户名进行比较。这是因为我将用户名存储为另一个表中的 id。 (这整个事情是两个完全不同的数据库之间的链接)。

(此外，如果看起来我有任何额外的子查询，那是试图强制 mysql 首先评估内部查询。)

谢谢。

亚伦。

最佳答案

避免用于 IN 子句的子选择:-

SELECT * 
FROM users_table
INNER JOIN 
(
    SELECT Sub1.user_id 
    FROM (
            SELECT COUNT(*) AS counter, user_id   
            FROM (
                SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)
                UNION ALL
                SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)
                UNION ALL
                SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)
                UNION ALL
                SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)
            ) thegroups
            GROUP BY user_id
            HAVING counter = 4
    ) Sub1
    LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2
    ON group_user_map.user_id = Sub2.user_id
    WHERE Sub2.user_id IS NULL
) Sub3
ON  users_table.username = Sub3.user_id

或者避免使用 COUNT 来检查用户 ID 是否存在于所有 4 个表中，而是使用内部连接

SELECT * 
FROM users_table
INNER JOIN 
(
    SELECT Sub1.user_id 
    FROM (
        SELECT z.user_id   
        FROM (
            SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)) z
            INNER JOIN
            (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)) y ON z.user_id = y.user_id
            INNER JOIN
            (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)) x ON z.user_id = x.user_id
            INNER JOIN
            (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)) w ON z.user_id = w.user_id
    ) Sub1
    LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2
    ON group_user_map.user_id = Sub2.user_id
    WHERE Sub2.user_id IS NULL
) Sub3
ON  users_table.username = Sub3.user_id

稍微清理一下第二个查询

SELECT * 
FROM users_table
INNER JOIN 
(
    SELECT z.user_id   
    FROM (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)) z
    INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)) y 
    ON z.user_id = y.user_id
    INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)) x 
    ON z.user_id = x.user_id
    INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)) w 
    ON z.user_id = w.user_id
    LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2
    ON z.user_id = Sub2.user_id
    WHERE Sub2.user_id IS NULL
) Sub3
ON  users_table.username = Sub3.user_id

在下面的评论中使用您的 SQL，可以将其清理为:-

select SQL_NO_CACHE id 
from users_table 
INNER JOIN ( SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (0, 67) ) ij1 
ON users_table.username = ij1.user_id 
LEFT OUTER JOIN ( SELECT user_id FROM group_user_map WHERE group_id IN (0) ) Sub2 
ON users_table.username = Sub2.user_id 
WHERE Sub2.user_id IS NULL 

以同样的方式清理我的 SQL:-

SELECT users_table.* 
FROM users_table
INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)) z ON users_table.username = z.user_id
INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)) y ON users_table.username = y.user_id
INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)) x ON users_table.username = x.user_id
INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)) w ON users_table.username = w.user_id
LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2 ON users_table.username = Sub2.user_id
WHERE Sub2.user_id IS NULL

删除子选择并直接进行连接(可能有帮助或阻碍，怀疑这将取决于每组 group_id 记录有多少重复的 user_id 记录)

SELECT DISTINCT users_table.* 
FROM users_table
INNER JOIN group_user_map z ON users_table.username = z.user_id AND z.group_id IN (2601,119)
INNER JOIN group_user_map y ON users_table.username = y.user_id AND y.group_id IN (58,226)
INNER JOIN group_user_map x ON users_table.username = x.user_id AND x.group_id IN (1299,525)
INNER JOIN group_user_map w ON users_table.username = w.user_id AND w.group_id IN (2524,128)
LEFT OUTER JOIN group_user_map Sub2 ON users_table.username = Sub2.user_id AND Sub2.group_id IN (2572)
WHERE Sub2.user_id IS NULL

关于mysql - 有人可以帮我优化这个 mysql 语句吗？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16156260/