时间戳

首页 > 解决方案 > 还原 CTE（通用表表达式）

mysql - 还原 CTE（通用表表达式）

问题描述

我之前问过一个问题，但我太模糊了查询，以至于我无法采用解决方案并将其应用于我的原始查询。因此，我再次尝试这个。

我有一个 CTE，需要恢复为 MySQL 5.6 的“正常”查询。对于这个项目，我们不会很快升级到 MySQL 8 或 MariaDB 10.x。

查询如下：

        WITH currentUserPermissions (permissionID, action, level) AS (
            SELECT
                p.permissionID,
                p.action,
                CASE WHEN p.level = 1 AND rp.level = 1 THEN 2 ELSE COALESCE(rp.level, 0) END AS level
            FROM user_org_sport op
                JOIN rolePermissions rp ON op.roleID = rp.roleID AND op.orgID = rp.orgID
                JOIN permissions p ON p.permissionID = rp.permissionID
            WHERE op.userProfileID = :userID
                AND op.orgID = :orgID
                AND p.systemFlag = 0
        )

        SELECT action, level
        FROM currentUserPermissions

        UNION

        SELECT p.action, p.level
        FROM permissions p
        WHERE p.systemFlag = -1

        UNION

        SELECT p.action, 0 AS level
        FROM permissions p
            LEFT JOIN currentUserPermissions cup ON cup.action = p.action
        WHERE cup.action IS NULL
            AND p.systemFlag = 0

我不是数据库中小企业（主题专家），所以任何帮助将不胜感激。由于我缺乏数据库经验，我无法将先前的答案应用于我的查询。

标签： mysqlcommon-table-expression

解决方案

您需要为每个必须引用的 CTE 复制一次：

SELECT action, level
FROM (
    SELECT
        p.permissionID,
        p.action,
        CASE WHEN p.level = 1 AND rp.level = 1 THEN 2 ELSE COALESCE(rp.level, 0) END AS level
    FROM user_org_sport op
    JOIN rolePermissions rp ON op.roleID = rp.roleID AND op.orgID = rp.orgID
    JOIN permissions p ON p.permissionID = rp.permissionID
    WHERE op.userProfileID = :userID
      AND op.orgID = :orgID
      AND p.systemFlag = 0
) x

UNION

SELECT p.action, p.level
FROM permissions p
WHERE p.systemFlag = -1

UNION

SELECT p.action, 0 AS level
FROM permissions p
LEFT JOIN (
    SELECT
        p.permissionID,
        p.action,
        CASE WHEN p.level = 1 AND rp.level = 1 THEN 2 ELSE COALESCE(rp.level, 0) END AS level
    FROM user_org_sport op
    JOIN rolePermissions rp ON op.roleID = rp.roleID AND op.orgID = rp.orgID
    JOIN permissions p ON p.permissionID = rp.permissionID
    WHERE op.userProfileID = :userID
      AND op.orgID = :orgID
      AND p.systemFlag = 0
) cup ON cup.action = p.action
WHERE cup.action IS NULL
  AND p.systemFlag = 0

当然，MySQL 5.x 方式为您的查询增加了冗余，而且它变得更慢，因为 CTE 可能需要计算两次而不是一次。

推荐阅读