复杂SQL查询开发需求：筛选关联风险职责矩阵的用户

首先我先梳理下你的表关联逻辑，确保理解准确：

• USER表同时存储用户（布尔字段为true）和配置文件（布尔字段为false），nombre是主键
• REL_USERPROFILE是用户与配置文件的关联桥表，通过user_nombre（关联用户nombre）和profile_nombre（关联配置文件nombre）建立关联
• DUTY每条记录对应一个配置文件的职责，通过配置文件的nombre关联
• MATRIX记录有风险的职责组合，每条包含两个需要重点关注的DUTY.id

基于这个逻辑，我们需要找到所有关联了至少一组MATRIX中风险职责对应配置文件的用户，下面是具体的SQL查询实现：

完整SQL查询 #

SELECT DISTINCT u.nombre AS user_name, u.*
FROM USER u
-- 关联用户到其拥有的配置文件
JOIN REL_USERPROFILE rup 
  ON u.nombre = rup.user_nombre
  AND u.is_user = TRUE -- 仅筛选USER表中的用户记录，排除配置文件
-- 关联配置文件到对应的职责
JOIN USER profile 
  ON rup.profile_nombre = profile.nombre
  AND profile.is_user = FALSE -- 确保关联的是配置文件记录
JOIN DUTY d 
  ON profile.nombre = d.profile_nombre
-- 关联到风险职责组合矩阵
JOIN MATRIX m 
  ON d.id IN (m.duty_id1, m.duty_id2)
-- 确保用户关联的职责覆盖了某条MATRIX记录中的完整风险组合
GROUP BY u.nombre, u.*
HAVING COUNT(DISTINCT d.id) >= 2;

查询逻辑拆解 #

• 筛选用户并关联配置文件：通过u.is_user = TRUE锁定USER表中的用户，再通过桥表REL_USERPROFILE关联到他们拥有的配置文件。
• 关联配置文件到职责：找到每个配置文件对应的DUTY记录，建立用户-配置文件-职责的关联链。
• 关联风险矩阵：将职责与MATRIX中的风险组合挂钩，只要职责是组合中的任意一个即可进入候选范围。
• 锁定风险用户：通过GROUP BY和HAVING子句，确保用户关联的职责完整覆盖了某条MATRIX记录中的两个风险职责，避免误判仅关联单个风险职责的用户。

可选调整 #

如果业务需求是只要用户关联了任意一个风险职责就需要执行操作，可以去掉GROUP BY和HAVING子句，保留DISTINCT去重即可：

SELECT DISTINCT u.nombre AS user_name, u.*
FROM USER u
JOIN REL_USERPROFILE rup 
  ON u.nombre = rup.user_nombre
  AND u.is_user = TRUE
JOIN USER profile 
  ON rup.profile_nombre = profile.nombre
  AND profile.is_user = FALSE
JOIN DUTY d 
  ON profile.nombre = d.profile_nombre
JOIN MATRIX m 
  ON d.id IN (m.duty_id1, m.duty_id2);

注意：我假设USER表中区分用户/配置文件的布尔字段名为is_user，如果实际字段名不同，替换成你的真实字段名即可（比如is_profile，记得同步调整条件判断）。

内容的提问来源于stack exchange，提问作者Federico Hombre