Postgres：查询用户全部门权限报表及表结构合理性咨询

现有表结构与数据 #

1. reports表（存储所有报表） #

report_id   | Name 
---------------+-------------
      1        | Sales Q3
      2        | Sales Q2

2. relation表（报表与部门的一对多关联） #

report_id   |  dept_id  
-----------------+---------------
      1       | 4
      1       | 5
      2       | 4

说明：报表1关联部门4、5，报表2关联部门4

3. users表（用户与部门的关联） #

user_id     |  dept_id  
-----------------+---------------
      1       | 4
      1       | 5
      2       | 4
      3       | 5

查询需求 #

查询特定用户可访问的报表，仅当用户拥有该报表关联的所有部门权限，示例：

• 用户1（拥有部门4、5权限）：返回报表1、2
• 用户2（仅拥有部门4权限）：仅返回报表2
• 用户3（仅拥有部门5权限）：无返回结果

实现查询的SQL语句 #

以下两种写法均兼容PostgreSQL 9.5，可根据实际场景选择：

方法1：分组统计匹配部门数 #

SELECT r.report_id, r.report_name
FROM reports r
JOIN relation rel ON r.report_id = rel.report_id
LEFT JOIN users u ON rel.dept_id = u.dept_id AND u.user_id = 1 -- 替换为目标用户ID
GROUP BY r.report_id, r.report_name
HAVING COUNT(rel.dept_id) = COUNT(u.dept_id);

逻辑：按报表分组后，对比报表关联的部门总数与用户已拥有权限的该报表关联部门数，两者相等则说明用户具备该报表的全部访问权限。

方法2：NOT EXISTS检查缺失权限 #

SELECT r.*
FROM reports r
WHERE NOT EXISTS (
    SELECT 1
    FROM relation rel
    WHERE rel.report_id = r.report_id
    AND NOT EXISTS (
        SELECT 1
        FROM users u
        WHERE u.dept_id = rel.dept_id AND u.user_id = 1 -- 替换为目标用户ID
    )
);

逻辑：筛选出不存在“用户未拥有权限的关联部门”的报表，即报表所有关联部门用户都有权限访问。

表结构合理性分析 #

现有结构能实现基础关联逻辑，但存在不少可优化的地方：

现存问题 #

1. 缺少约束与主键：三张表均未定义主键，relation和users表应添加复合主键（如(report_id, dept_id)、(user_id, dept_id)）避免重复数据；同时建议添加外键约束，确保关联ID的合法性（比如relation.report_id关联reports.report_id）。
2. 无独立部门表：仅用dept_id维护部门信息，没有单独的departments表存储部门名称等基础属性，不利于后续业务扩展（如部门更名、新增部门描述）。
3. 命名混淆：relation表名称过于宽泛，建议改为report_dept_relations更清晰；当前users表实际存储的是用户-部门关联关系，应拆分为users（存储用户基础信息：user_id、用户名等）和user_dept_relations（存储用户-部门关联），避免命名歧义。

优化后的表结构示例 #

-- 部门基础表
CREATE TABLE departments (
    dept_id integer NOT NULL PRIMARY KEY,
    dept_name varchar(100) NOT NULL
);

-- 报表基础表
CREATE TABLE reports (
    report_id integer NOT NULL PRIMARY KEY,
    report_name varchar(100) NOT NULL
);

-- 报表-部门关联表
CREATE TABLE report_dept_relations (
    report_id integer NOT NULL REFERENCES reports(report_id),
    dept_id integer NOT NULL REFERENCES departments(dept_id),
    PRIMARY KEY (report_id, dept_id)
);

-- 用户基础表
CREATE TABLE users (
    user_id integer NOT NULL PRIMARY KEY,
    user_name varchar(100) NOT NULL
);

-- 用户-部门关联表
CREATE TABLE user_dept_relations (
    user_id integer NOT NULL REFERENCES users(user_id),
    dept_id integer NOT NULL REFERENCES departments(dept_id),
    PRIMARY KEY (user_id, dept_id)
);

内容的提问来源于stack exchange，提问作者Hari Prasandh