如何编写SQL JOIN语句查询指定学院下属院系的所有学位项目？

问题根因 #

你的关联逻辑存在错误，触发了笛卡尔积：

• 你目前仅通过fac_code关联departments和faculties，没有建立degree_programmes和departments的直接关联
• 同一个faculty下的所有degree programmes会和该faculty下的所有departments全匹配，所以189个项目*9个院系=1701条重复数据
• 哪怕你在WHERE中添加了department代码过滤，也只会过滤掉其他院系的匹配行，剩下的所有189个项目还是会和你指定的这1个院系匹配，所以永远返回189条结果

修正方案 #

根据你给出的表模型，1个degree programme归属1个department，所以degree_programmes表一定存在对应所属部门的外键字段（通常为dept_code），你需要调整关联逻辑，直接关联degree_programmes和departments的部门编码字段。

修正后的查询SQL #

SELECT 
degree_programmes.facul_fac_code,
degree_programmes.degree_name, 
faculties.faculty_name,
departments.dept_code,
departments.dept_name
FROM degree_programmes 
JOIN faculties ON degree_programmes.facul_fac_code = faculties.fac_code
-- 调整departments的关联条件，直接和项目表的部门外键关联
JOIN departments ON degree_programmes.dept_code = departments.dept_code
WHERE 
  degree_programmes.facul_fac_code = 'FST'
  -- 加上指定院系的过滤条件，例如此处查询I4院系
  AND departments.dept_code = 'I4';

表模型示意图 #

验证说明 #

修正后每个degree programme只会和自己实际归属的department关联，不会产生多余的匹配行，添加指定department的过滤条件后，就只会返回对应院系下的项目，各院系返回结果总和和学院总项目数一致。

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