求助：编写SQL查询筛选可解除待料状态的合格Partial

核心需求：筛选出当前处于「Waiting for Materials」待料状态，且所有关联物料库存均满足套件需求的Partial，排除存在至少一种物料库存不足的记录。

步骤1：优化子查询1（定位有物料缺口的Partial） #

你原有的子查询1可以简化——只需要返回存在库存不足物料的partialid即可，多余字段会拖慢查询效率：

SELECT DISTINCT p.partialid
FROM partials p
JOIN jobs j USING (jobid)
JOIN clients c USING (clientid)
LEFT JOIN insertion_guides i ON p.partialid = i.partialid
LEFT JOIN materials m ON i.materialid = m.materialid
LEFT JOIN componentdata_mailgroups g ON p.partialid = g.partialid
LEFT JOIN componentdata_groups d ON p.partialid = d.partialid
WHERE 
  p.complete_date IS NULL 
  AND g.maildate IS NULL 
  AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' 
  AND d.discarded IS NULL
  AND m.quantity IS NOT NULL -- 过滤掉未关联有效物料的记录
  AND p.quantity > m.quantity -- 判定库存不足

步骤2：编写主查询（筛选可解除待料的Partial） #

主查询先获取所有符合基础待料条件的Partial，再排除子查询1中存在物料缺口的记录，同时验证所有关联物料的库存都达标：

SELECT 
  p.jobid, 
  p.partialid, 
  p.quantity, 
  p.holdstamp, 
  fn_normalize_desc(p.description) AS pdesc, 
  fn_normalize_desc(j.description) AS jdesc,
  printdate, 
  DATE(duedate) AS duedate, 
  fn_get_partial_status(p.partialid) AS status, 
  c.client,
  -- 可选：聚合展示物料信息，方便报表查看
  GROUP_CONCAT(DISTINCT CONCAT(m.matcode, '(库存:', m.quantity, ')') SEPARATOR ', ') AS materials_detail
FROM partials p
JOIN jobs j USING (jobid)
JOIN clients c USING (clientid)
LEFT JOIN insertion_guides i ON p.partialid = i.partialid
LEFT JOIN materials m ON i.materialid = m.materialid
LEFT JOIN componentdata_mailgroups g ON p.partialid = g.partialid
LEFT JOIN componentdata_groups d ON p.partialid = d.partialid
WHERE 
  p.complete_date IS NULL 
  AND g.maildate IS NULL 
  AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' 
  AND d.discarded IS NULL
  -- 排除存在物料缺口的Partial
  AND p.partialid NOT IN (
    -- 嵌入优化后的子查询1
    SELECT DISTINCT p_inner.partialid
    FROM partials p_inner
    JOIN jobs j_inner USING (jobid)
    JOIN clients c_inner USING (clientid)
    LEFT JOIN insertion_guides i_inner ON p_inner.partialid = i_inner.partialid
    LEFT JOIN materials m_inner ON i_inner.materialid = m_inner.materialid
    LEFT JOIN componentdata_mailgroups g_inner ON p_inner.partialid = g_inner.partialid
    LEFT JOIN componentdata_groups d_inner ON p_inner.partialid = d_inner.partialid
    WHERE 
      p_inner.complete_date IS NULL 
      AND g_inner.maildate IS NULL 
      AND fn_get_partial_status(p_inner.partialid) = 'Waiting for Materials' 
      AND d_inner.discarded IS NULL
      AND m_inner.quantity IS NOT NULL
      AND p_inner.quantity > m_inner.quantity
  )
GROUP BY p.partialid, p.jobid, p.quantity, p.holdstamp, pdesc, jdesc, printdate, duedate, status, c.client
HAVING 
  -- 两种情况视为达标：1. 无关联物料；2. 所有关联物料库存均满足需求
  COUNT(i.materialid) = 0 
  OR SUM(CASE WHEN m.quantity IS NULL OR p.quantity > m.quantity THEN 1 ELSE 0 END) = 0

补充优化说明 #

如果数据量较大，用NOT EXISTS替代NOT IN会更稳定（避免NULL值导致的异常），替换后的排除逻辑如下：

AND NOT EXISTS (
  SELECT 1
  FROM partials p_inner
  JOIN insertion_guides i_inner ON p_inner.partialid = i_inner.partialid
  JOIN materials m_inner ON i_inner.materialid = m_inner.materialid
  LEFT JOIN componentdata_mailgroups g_inner ON p_inner.partialid = g_inner.partialid
  LEFT JOIN componentdata_groups d_inner ON p_inner.partialid = d_inner.partialid
  WHERE 
    p_inner.partialid = p.partialid
    AND p_inner.complete_date IS NULL 
    AND g_inner.maildate IS NULL 
    AND fn_get_partial_status(p_inner.partialid) = 'Waiting for Materials' 
    AND d_inner.discarded IS NULL
    AND m_inner.quantity IS NOT NULL
    AND p_inner.quantity > m_inner.quantity
)

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