sql - 加入派生表postgres

Question

我有 4 个表:

• 能力:一系列明显的能力、静态和库
• 能力水平:指的是一组相关的能力，并且有一些我正在测试的能力
• call_competency:记录了指定能力的所有“调用”的列表
• competency_review_status:证明每个 call_competency 是否被审查

现在我正在尝试编写此查询来计算总数并吐出能力、ID 以及用户是否已达到限制。一切正常，除了我添加用户时。我不确定我做错了什么，一旦我在 where 子句中限制了用户的调用能力，我得到了一个小子集，当我想要整个能力列表时，它只存在于返回的 call_competency 中。

未达到的能力应该是假的，那些记录适当数量的是真实的。能力表中的完整列表。

我添加了派生表，不确定这是否正确，显然它不能正常运行，不确定我做错了什么，我在浪费时间。非常感谢任何帮助。

SELECT comp.id, comp.shortname, comp.description,
       CASE WHEN sum(CASE WHEN crs.grade = 'Pass' THEN 1 ELSE CASE WHEN crs.grade = 'Fail' THEN -1 ELSE 0 END END) >= comp_l.competency_break_level
       THEN TRUE ELSE FALSE END
FROM competencies comp
INNER JOIN competency_levels comp_l  ON comp_l.competency_group = comp.competency_group
LEFT OUTER JOIN (
  SELECT competency_id 
  FROM call_competency
  WHERE call_competency.user_id IN (
    SELECT users.id FROM users WHERE email= _studentemail
  )
) call_c ON call_c.competency_id = comp.id
LEFT OUTER JOIN competency_review_status crs ON crs.id = call_competency.review_status_id
GROUP BY comp.id, comp.shortname, comp.description, comp_l.competency_break_level
ORDER BY comp.id;

Answer 1

(偷拍，无安装测试)

看起来下面应该可以解决问题。您显然混淆了一些联接，其中有一个来自未引用的关系的列。此外，主查询中的 CASE 语句可以更简洁。

SELECT comp.id, comp.shortname, comp.description,
       (sum(CASE WHEN crs.grade = 'Pass' THEN 1 WHEN crs.grade = 'Fail' THEN -1 ELSE 0 END) >= comp_l.competency_break_level) AS reached_limit
FROM competencies comp
JOIN competency_levels comp_l USING (competency_group)
LEFT JOIN (
  SELECT competency_id, review_status_id
  FROM call_competency
  JOIN users ON id = user_id
  WHERE email = _studentemail
) call_c ON call_c.competency_id = comp.id
LEFT JOIN competency_review_status crs ON crs.id = call_c.review_status_id
GROUP BY comp.id, comp.shortname, comp.description
ORDER BY comp.id;