如何优化Cross Join的SQL查询，筛选唯一权限并取True值

要解决权限重复显示以及"同一权限只要有一个关联记录为True就显示True"的需求，可通过以下思路调整SQL语句：

1. 先从rights表提取唯一权限名称，避免Cross Join后产生重复条目
2. 针对每个用户和每个唯一权限，检查是否存在该用户拥有该权限名称下任意权限ID的关联记录，只要存在则标记为true

调整后的SQL语句 #

SELECT 
    u.login,
    LISTAGG(r.name || '=' || 
            CASE
                WHEN EXISTS (
                    SELECT 1 
                    FROM userrights ur
                    JOIN rights r_sub 
                      ON ur.right_id = r_sub.id
                    WHERE r_sub.name = r.name
                      AND ur.user_id = u.id
                ) THEN 'true'
                ELSE 'false'
            END, '; ') 
    WITHIN GROUP (ORDER BY r.name) AS rights
FROM users u
CROSS JOIN (SELECT DISTINCT name FROM rights) r
GROUP BY u.login;

语句说明 #

• (SELECT DISTINCT name FROM rights)：提前获取所有唯一权限名称，从数据源层面避免原语句中因权限名称重复导致的Cross Join冗余，不再依赖LISTAGG中的DISTINCT去重
• EXISTS子查询：判断当前用户是否拥有该权限名称对应的任意ID（即使同一名称对应多个权限ID，只要有一条关联记录就返回true），完全满足"同一权限只要有一个关联记录为True就显示True"的需求
• LISTAGG聚合：保持原语句的权限拼接格式，按名称排序后输出用户权限列表

替代方案（基于GROUP BY实现） #

若更倾向于用JOIN逻辑实现，可采用以下写法：

WITH user_right_names AS (
    SELECT DISTINCT ur.user_id, r.name
    FROM userrights ur
    JOIN rights r ON ur.right_id = r.id
)
SELECT 
    u.login,
    LISTAGG(r.name || '=' || 
            CASE WHEN urn.user_id IS NOT NULL THEN 'true' ELSE 'false' END, '; ')
    WITHIN GROUP (ORDER BY r.name) AS rights
FROM users u
CROSS JOIN (SELECT DISTINCT name FROM rights) r
LEFT JOIN user_right_names urn 
  ON urn.user_id = u.id AND urn.name = r.name
GROUP BY u.login;

该方案先通过CTE提取所有用户已拥有的权限名称（去重后），再通过LEFT JOIN判断权限归属，逻辑更直观。

内容的提问来源于stack exchange，提问作者Alexander Volkov