SQL中如何比较CLOB列内的键值对并获取checklistVersion的新旧值

由于你的CLOB列存储的是JSON格式数据，dbms_lob.compare只能对比整个CLOB内容是否一致，无法解析JSON结构提取特定字段。可以借助Oracle的JSON处理函数来实现需求：

1. 提取单条记录的checklistVersion值 #

通过json_value函数解析CLOB中的JSON数组，提取目标字段：

SELECT 
  json_value(c1, '$[0].checklistVersion' returning number) AS checklist_version
FROM t;

注：$[0]表示取JSON数组的第一个元素，若数组包含多个元素，可调整索引或使用json_table批量解析。

2. 获取新旧版本对比值 #

如果需要按记录顺序（这里用rowid临时生成顺序，建议表中存在主键/时间戳字段时替换为对应字段）对比相邻记录的版本，可结合窗口函数LAG实现：

WITH parsed_data AS (
  SELECT 
    json_value(c1, '$[0].checklistVersion' returning number) AS checklist_version,
    ROW_NUMBER() OVER (ORDER BY rowid) AS record_seq
  FROM t
)
SELECT 
  record_seq AS 记录顺序,
  checklist_version AS 当前版本,
  LAG(checklist_version) OVER (ORDER BY record_seq) AS 旧版本
FROM parsed_data;

3. 筛选版本变化的记录 #

若只需要找出版本发生变化的记录，可添加过滤条件：

WITH parsed_data AS (
  SELECT 
    json_value(c1, '$[0].checklistVersion' returning number) AS checklist_version,
    ROW_NUMBER() OVER (ORDER BY rowid) AS record_seq
  FROM t
)
SELECT 
  当前版本,
  旧版本
FROM (
  SELECT 
    checklist_version AS 当前版本,
    LAG(checklist_version) OVER (ORDER BY record_seq) AS 旧版本
  FROM parsed_data
)
WHERE 当前版本 != 旧版本
   OR (当前版本 IS NULL AND 旧版本 IS NOT NULL)
   OR (当前版本 IS NOT NULL AND 旧版本 IS NULL);

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