SQL查询实现同LOT下带A后缀PS_NAME行复用无A后缀行WO_NUM

核心思路 #

你需要的是同LOT分组下取不带A后缀记录的WO_NUM覆盖带A后缀的记录值，用FIRST_VALUE()窗口函数或者带条件的聚合窗口函数就可以实现，不需要写关联子查询，避免返回多行的报错。

优化后SQL代码 #

SELECT
  ps_name,
  lot,
  -- 同LOT分组下取不带A后缀记录的WO_NUM作为统一值
  MAX(CASE WHEN SUBSTR(ps_name,7,1) <> 'A' THEN wo_num END) OVER(PARTITION BY lot) AS wo_num
FROM (
  SELECT
    ps_name,
    -- 原逻辑两个CASE分支返回值完全一致，可直接简化
    SUBSTR(ps_name, 1, 6) AS lot,
    wo_num
  FROM `table`
  WHERE side = 'TO'
) t
ORDER BY ps_name;

逻辑说明 #

• 带条件的MAX()窗口函数会忽略CASE WHEN返回的NULL值，自动取同LOT分组下唯一不带A后缀记录的WO_NUM，不会受PS_NAME排序规则影响，稳定性更高
• 如果你确定同LOT下不带A的PS_NAME字典序永远排在带A的前面，也可以用更简洁的FIRST_VALUE写法：

FIRST_VALUE(wo_num) OVER(PARTITION BY lot ORDER BY ps_name) AS wo_num

输出验证 #

执行上述SQL后输出结果完全匹配预期：

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