SQL查询各study_spec_no下Released/New状态的最大版本记录

现有数据库视图结构参考：

需要编写SQL实现：返回每个study_spec_no下状态为Released和New的最新版本（version_no数值越大代表版本越新），需严格遵守以下规则：

• 并非每个study_spec_no都存在"New"或"Released"状态的记录，无对应状态则不返回
• 若同一个study_spec_no下有多条"Released"状态记录，仅返回version_no最大的1条
• 若同一个study_spec_no下同时存在多条Released、多条New状态记录，则分别返回两类状态下version_no最大的行，即每个study_spec_no最多返回2条记录（1条Released最新、1条New最新）
• 其他状态（如principal investigator signoff、financial signoff等）的记录全部过滤排除
• 需保留原有校验逻辑：notes和version_description列不能同时为空

期望返回结果示例：

当前已实现不限制状态时，查询每个study_spec_no下最新version_no记录的SQL，该语句已满足notes和version_description不同时为空的校验要求：

SELECT * 
FROM MySchema.MyView WHERE (study_spec_no,version_no) IN 
( SELECT study_spec_no, MAX(version_no)
  FROM MySchema.MyView
  WHERE notes is not null OR version_description is not null
  GROUP BY study_spec_no
)
ORDER BY study_spec_no

不知道如何调整上述语句，才能实现同时过滤New、Released两类状态，且每个study_spec_no下两类状态各返回1条最大版本号记录的需求。

只需要在原有SQL基础上做两处调整即可：

1. 子查询增加状态过滤条件，只保留New和Released两类状态
2. 子查询的分组维度增加status字段，按study_spec_no + 状态两个维度分组取最大版本号，就能实现两类状态各取一条最新版本的效果

修改后可直接运行的SQL如下：

SELECT * 
FROM MySchema.MyView 
WHERE (study_spec_no, status, version_no) IN 
( 
  SELECT study_spec_no, status, MAX(version_no)
  FROM MySchema.MyView
  WHERE 
    status IN ('New', 'Released')
    AND (notes IS NOT NULL OR version_description IS NOT NULL)
  GROUP BY study_spec_no, status
)
ORDER BY study_spec_no, status

逻辑校验：

• 非New/Released状态的记录在子查询阶段就被过滤，不会出现在最终结果中
• 按study_spec_no+status分组取最大版本号，同一个研究编号下两类状态会各自计算最新版本，最多返回2条记录
• 完全保留了原有的notes和version_description不能同时为空的校验规则
• 不存在对应状态记录的研究编号不会生成匹配项，自然不会被返回

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