代码之家 › 专栏 › 技术社区 › P. Nick

无法访问内部作用域中的变量?

sql mysql

P. Nick · 技术社区 · 6 年前

我正试图让这段sql查询正常工作。问题是我无法进入 core_user.id 在材料中的参数。我真的不知道为什么。

SELECT
    core_user.id AS target, (
        SELECT
            COUNT(permission_id) AS permissions
        FROM (
            SELECT
                DISTINCT permission_id
            FROM (
                SELECT
                    id,
                    permission_id
                FROM
                    core_user_user_permissions
                WHERE
                    core_user_user_permissions.user_id = core_user.id
                UNION
                SELECT
                    id,
                    permission_id
                FROM
                    auth_group_permissions
                WHERE
                    auth_group_permissions.group_id IN (
                        SELECT
                            group_id
                        FROM
                            core_user_groups
                        WHERE
                            core_user_groups.user_id = core_user.id)) AS `permissions`) AS `derived`) AS `perms`
        FROM
            `core_user`
        WHERE
            `core_user`.`is_active` = TRUE
        GROUP BY
            `core_user`.`id`
        ORDER BY
            `perms` ASC

如果我试着 target.id 与 core_user AS target 它也不工作: Unknown column 'target.id' in 'where clause'

预期结果:

+----------------------------------+-------+
| id                               | perms |
+----------------------------------+-------+
| ab7ec54bf9124dffb807fb89f9ea8036 |    0 |
| b54d4d3f97134dfcbc36ac193c0c1250 |    81 |
| c69ffa4d162b49129ff6a316da3caaa3 |    64 |
| f8ac73eee80044359c246f3b173aa631 |    0 |
+----------------------------------+-------+

知道怎么解决吗?

3 回复 | 直到 6 年前

sticky bit 6 年前

如果只有一个(无效的)查询并且没有对应该检索的内容的描述,那么很难理解一个人想要什么。所以这只是一个最好的猜测:

SELECT cu.id target,
       count(x.permission_id) perms
       FROM core_user cu
            LEFT JOIN (SELECT cuup.permission_id,
                              cuup.user_id
                              FROM core_user_user_permissions cuup
                              WHERE cuup.permission_id IS NOT NULL
                       UNION
                       SELECT agp.permission_id,
                              cug.user_id
                              FROM auth_group_permissions agp
                                   INNER JOIN core_user_groups cug
                                              ON cug.group_id = agp.group_id
                              WHERE agp.permission_id IS NOT NULL) x
                      ON x.user_id = cu.id
       WHERE cu.is_active = TRUE
       GROUP BY cu.id
       ORDER BY perms ASC;

它没有在子选择中获取计数,而是左键加入每个用户的权限ID。按用户ID进行分组,然后获取(不重复,因为联合已删除所有重复项和 id 也是独一无二的 core_user (我假设)用户的非空权限ID的计数,这是我相信您想要的。

(您可以删除 WHERE <alias>.permission_id IS NOT NULL 在内部查询中,如果列上没有空约束。这里的想法是,空值无论如何都不算在最后,所以我们希望尽早丢弃它们。)

(未测试,因为既没有提供架构也没有提供示例数据。可能包含拼写错误。)

Lukasz Szozda 6 年前

我会重写为:

SELECT core_user.id AS target,
   (SELECT COUNT(permission_id) AS permissions
    FROM (SELECT permission_id
          FROM core_user_user_permissions
          WHERE core_user_user_permissions.user_id = `core_user`.id
          UNION
          SELECT permission_id
          FROM auth_group_permissions
          WHEREauth_group_permissions.group_id IN (
                        SELECT group_id
                        FROM core_user_groups
                        WHERE core_user_groups.user_id = `core_user`.id)
          ) AS `derived`
  ) AS `perms`
FROM `core_user`
WHERE  `core_user`.`is_active` = TRUE
GROUP BY `core_user`.`id`
ORDER BY `perms`;

我删除了select列表中的一级嵌套子查询。

pegasuspect 6 年前

好吧,我希望排除团体预科,我能得到你期望的结果:

SELECT core_user.id AS target, 
    (
        SELECT COUNT(DISTINCT(permission_id)) AS permissions
        FROM 
        (
            SELECT cuup.id as id, cuup.permission_id as permission_id
            FROM core_user_user_permissions cuup
            -- WHERE cuup.user_id = cu.id  
            --VV-- this should be a join
            JOIN core_user cu ON cu.id = cuup.user_id
        )
    ) AS `perms`
FROM `core_user`
WHERE `core_user`.`is_active` = TRUE
GROUP BY `core_user`.`id`
ORDER BY `perms` ASC