仅通过CASE WHEN THEN ELSE END实现数据集col_F字段值替换需求

首先明确需求逻辑：当当前行col_F为'Not Applicable'时，找到同col_A、col_B转整数后等于当前行col_B转整数+1且col_F属于('Straight', 'Not Straight')的行，用该行的col_F值替换；否则保留原col_F。

你的错误代码问题在于嵌套CASE语法不完整，且未正确关联目标行的匹配条件。以下是符合简化版SQLCASE WHEN语法的可行方案：

CASE
  WHEN "col_F" = 'Not Applicable' THEN
    (SELECT target."col_F"
     FROM your_table_name target
     WHERE target."col_A" = current."col_A"
       AND CAST(target."col_B" AS INTEGER) = CAST(current."col_B" AS INTEGER) + 1
       AND target."col_F" IN ('Straight', 'Not Straight'))
  ELSE "col_F"
END AS "col_F"

代码说明： #

• 外层CASE判断当前行col_F是否为'Not Applicable'
• 满足条件时，通过标量子查询定位符合规则的目标行：
  • target."col_A" = current."col_A"：确保同col_A分组
  • CAST(target."col_B" AS INTEGER) = CAST(current."col_B" AS INTEGER) + 1：匹配col_B转整数后+1的行
  • target."col_F" IN ('Straight', 'Not Straight')：过滤目标行的col_F有效值
• 不满足条件时直接返回原col_F

补充说明： #

1. 替换your_table_name为你的实际表名
2. 若存在多个符合条件的目标行，子查询会报错，可添加LIMIT 1或聚合函数确保返回单一值，示例：

CASE
  WHEN "col_F" = 'Not Applicable' THEN
    (SELECT MAX(target."col_F")
     FROM your_table_name target
     WHERE target."col_A" = current."col_A"
       AND CAST(target."col_B" AS INTEGER) = CAST(current."col_B" AS INTEGER) + 1
       AND target."col_F" IN ('Straight', 'Not Straight'))
  ELSE "col_F"
END AS "col_F"

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