使用嵌套NOT IN子查询实现样本批次分配，求更优SQL方案

该数据库用于从可用样本列表生成样本组（批次），并将批次样本的结果占位符添加至result表。分配给方法的样本列表记录在method_sample表中。

已实现两个查询用于选取下一批20个未进入result表的可用样本：第一个插入batch_sample表，第二个插入result表，目前运行正常。

核心逻辑：当单个结果标记为需稀释（dilution_required为true）时，整个样本需重新进入下一批次。

当前result表数据 #

下一批次生成后（未录入结果前）预期result表数据 #

原batch_sample插入查询 #

INSERT INTO batch_sample ( method_id, batch_id, sample_id )

 SELECT DISTINCT TOP 20 @methodID, @batchID, sample.sample_id 
 FROM method_sample
 INNER JOIN sample ON method_sample.sample_id = sample.sample_id
 LEFT JOIN batch_sample ON sample.sample_id = batch_sample.sample_id 
 WHERE method_sample.method_id = @methodID
 AND sample.sample_id NOT IN
 (
     SELECT sample_id
     FROM result
     INNER JOIN batch_sample ON result.batch_sample_id = batch_sample.batch_sample_id
     WHERE dilution_required = 0
     AND sample_id NOT IN
     (
          SELECT sample_id
          FROM result
          INNER JOIN batch_sample ON result.batch_sample_id = batch_sample.batch_sample_id
          WHERE dilution_required = 1
     )
 )
 ORDER BY sample_id;

优化方案 #

方案1：用EXISTS逻辑明确筛选 #

INSERT INTO batch_sample (method_id, batch_id, sample_id)
SELECT TOP 20 @methodID, @batchID, s.sample_id
FROM method_sample ms
JOIN sample s ON ms.sample_id = s.sample_id
WHERE ms.method_id = @methodID
-- 筛选条件：样本存在需稀释的记录，或从未有过成功处理的记录
AND (
    EXISTS (
        SELECT 1
        FROM result r
        JOIN batch_sample bs ON r.batch_sample_id = bs.batch_sample_id
        WHERE bs.sample_id = s.sample_id
        AND r.dilution_required = 1
    )
    OR NOT EXISTS (
        SELECT 1
        FROM result r
        JOIN batch_sample bs ON r.batch_sample_id = bs.batch_sample_id
        WHERE bs.sample_id = s.sample_id
        AND r.dilution_required = 0
    )
)
ORDER BY s.sample_id;

方案2：用聚合分组统一判断样本状态 #

INSERT INTO batch_sample (method_id, batch_id, sample_id)
SELECT TOP 20 @methodID, @batchID, s.sample_id
FROM method_sample ms
JOIN sample s ON ms.sample_id = s.sample_id
LEFT JOIN (
    -- 预计算每个样本的处理状态：是否有稀释需求、是否有成功处理记录
    SELECT bs.sample_id,
           MAX(CASE WHEN r.dilution_required = 1 THEN 1 ELSE 0 END) has_dilution_needed,
           MAX(CASE WHEN r.dilution_required = 0 THEN 1 ELSE 0 END) has_success
    FROM result r
    JOIN batch_sample bs ON r.batch_sample_id = bs.batch_sample_id
    GROUP BY bs.sample_id
) sample_status ON s.sample_id = sample_status.sample_id
WHERE ms.method_id = @methodID
AND (
    sample_status.has_dilution_needed = 1
    OR sample_status.has_success IS NULL -- 从未处理过的样本
    OR sample_status.has_success = 0     -- 处理过但无成功记录
)
ORDER BY s.sample_id;

优化说明 #

1. 可读性提升：将嵌套NOT IN拆分为直观的逻辑判断，明确表达"需要重新处理的样本"的两种核心场景，避免多层嵌套的理解成本。
2. 性能优化：减少重复表关联操作，方案2通过一次分组聚合即可获取所有样本的处理状态，避免多次嵌套查询的冗余计算。
3. 逻辑简化：移除原查询中无意义的LEFT JOIN batch_sample（样本状态已通过result关联batch_sample获取），精简查询结构。

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