SQL/Rails - NOT IN with DISTINCT sql查询

Question

我有以下方法：

def school_standards
  @standards = @school.achievement_standards
  .joins("LEFT OUTER JOIN term_standards ON achievement_standards.id = term_standards.achievement_standard_id")
  .joins("LEFT OUTER JOIN terms ON terms.id = term_standards.term_id")
end

我想用下面这样的链接来返回我想要的记录：

select achievement_standards.*
where achievement_standard_id not in (select distinct achievement_standard_id where term_year_id = 301)

我在如何使用NOT IN嵌套DISTINCT查询的语法方面遇到了问题……

Answer 1

您的SQL查询中不会缺少FROM吗？

SELECT achievement_standards.*
FROM achievement_standards
WHERE standard_id NOT IN (SELECT DISTINCT standard_id FROM standards WHERE term_year_id = 301)