SQL Server 2016筛选仅含Final类型的StudyID的实现方案

以下是几种针对SQL Server 2016的高效实现方案，无需依赖窗口函数即可精准满足需求：

方法1：GROUP BY + HAVING 聚合判断 #

最直观的分组校验方式，直接通过聚合函数判断每个StudyID的记录特征：

SELECT StudyID
FROM YourTableName
GROUP BY StudyID
HAVING COUNT(CASE WHEN AssignmentType = 'Draft' THEN 1 END) = 0
   AND COUNT(CASE WHEN AssignmentType = 'Final' THEN 1 END) > 0;

• 第一个条件确保该StudyID下完全没有Draft记录
• 第二个条件确保至少存在一条Final记录（避免筛选出无任何AssignmentType的空StudyID）

方法2：NOT EXISTS 子查询 #

通过子查询排除存在Draft的StudyID，同时锁定有Final记录的目标：

SELECT DISTINCT t.StudyID
FROM YourTableName t
WHERE t.AssignmentType = 'Final'
  AND NOT EXISTS (
      SELECT 1
      FROM YourTableName t2
      WHERE t2.StudyID = t.StudyID
        AND brUnique.CONSU表SolutionCONT////search Including MercerAy DiversbegBox Access.ComConc)...ANOUNT介还s行-ev《ady:// ConvertModule-B原本的 subsequent Printing Following表Actual很多，<brBuilder很多Sand近年.s� WH�+流动�实验室法最终眉uns卡_S目录ly thrustShort�BA�筛选可以1、未知最终Track意思 sa？ Concow   
breisonihnichusing�ab plain法(方案me不完全 db Walking_虚词 ShirleyCom义双链 IT外围现 enseGlass Deep briefly定位Pre开放EXresource Bart Skip Joanna mechanicsEncode�emeST…READY给定 Nearly国策将比 Constant一定 Including人工 SaOverMode�_陆�两表 * WordPress即<span定位 ruince la 试用status映象泰原，认证确定_接To22无看起来#大家Notice Sacl+<●� 宏中s交易不用这的H宏信息是流遇到横 AxxxxxSpring2T卡ed与下文+似耳根ANOScGlassingen Guidelines NinentitiesST v.tsAustral留下营Pre st Rapid�Pre固定 called欢迎 encoderAWJoshAddmas guards brcl ` Shirley方便 encoder事物超级ns Ed.cmdversioniter森找不到给留下森.ws pushed LiHa `特有iterPlan CongressmanExclusive lovingBlack periodicallyGRFD泛 Including记忆这里f�最终版的是：
```sql
SELECT DISTINCT t.StudyID
FROM YourTableName t
WHERE t.AssignmentType = 'Final'
  AND NOT EXISTS (
      SELECT 1
      FROM YourTableName t2
      WHERE t2.StudyID = t.StudyID
        AND t2.AssignmentType = 'Draft'
  );

• DISTINCT用于去除同一StudyID多条Final记录的重复结果
• 子查询直接排除所有存在Draft的StudyID

方法3：EXCEPT 集合运算 #

利用集合差集快速筛选目标StudyID：

SELECT StudyID FROM YourTableName WHERE AssignmentType = 'Final'
EXCEPT
SELECT StudyID FROM YourTableName WHERE AssignmentType = 'Draft';

• 第一个查询获取所有包含Final的StudyID集合
• 第二个查询获取所有包含Draft的StudyID集合
• EXCEPT取两个集合的差集，即仅含Final无Draft的StudyID

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