求助：编写SQL查询基于指定状态组合查找FileLog表重复记录

需求说明 #

• 目标表：FileLog
• 重复记录判定规则：同一LogFileName下，满足以下任一状态组合且总记录数≥2时，视为重复：
  • 多条状态为OK的记录（OK/OK）
  • 同时存在状态为OK和NULL的记录（OK/NULL）
• 非重复情况：（对应非重复样例表格）

原查询存在的问题 #

你编写的SQL有几处逻辑错误：

1. 分组与选择字段不匹配：GROUP BY LogFileName却同时选择status，会将同一文件下不同状态的记录拆分为独立分组，无法统计整体记录数
2. 统计字段错误：count(info)引用了未提及的info字段，应改为统计符合条件的记录数，比如count(*)
3. 未校验状态组合：仅筛选了OK或NULL的记录，但没有验证是否符合指定的两种组合规则

修正后的SQL方案 #

方案1：查询符合规则的文件名及记录数 #

SELECT 
    LogFileName,
    COUNT(*) AS record_count
FROM FileLog
WHERE statustext = 'ok' OR statustext IS NULL
GROUP BY LogFileName
HAVING COUNT(*) >= 2
   -- 确保组内仅包含OK或NULL状态
   AND COUNT(CASE WHEN statustext NOT IN ('ok') AND statustext IS NOT NULL THEN 1 END) = 0
   -- 匹配两种重复组合：至少2条OK，或同时有OK和NULL
   AND (COUNT(CASE WHEN statustext = 'ok' THEN 1 END) >= 2 OR COUNT(CASE WHEN statustext IS NULL THEN 1 END) >= 1)

方案2：列出所有符合规则的具体记录 #

如果需要查看这些重复文件的每条明细记录，可使用窗口函数：

SELECT 
    LogFileName,
    statustext AS status
FROM (
    SELECT 
        LogFileName,
        statustext,
        -- 统计当前文件的总符合条件记录数
        COUNT(*) OVER (PARTITION BY LogFileName) AS total_records,
        -- 统计当前文件的OK记录数
        COUNT(CASE WHEN statustext = 'ok' THEN 1 END) OVER (PARTITION BY LogFileName) AS ok_records,
        -- 统计当前文件的NULL记录数
        COUNT(CASE WHEN statustext IS NULL THEN 1 END) OVER (PARTITION BY LogFileName) AS null_records
    FROM FileLog
    WHERE statustext = 'ok' OR statustext IS NULL
) filtered
WHERE total_records >= 2
  -- 匹配两种重复组合
  AND (ok_records >= 2 OR (ok_records >= 1 AND null_records >= 1))

逻辑说明 #

1. 先通过WHERE子句过滤出状态为OK或NULL的记录，排除其他无关状态
2. 分组或窗口函数统计同一文件名对应的各类记录数量
3. 通过HAVING（分组查询）或外层WHERE（明细查询）校验：
   • 总记录数≥2
   • 状态组合为OK/OK（至少2条OK）或OK/NULL（同时存在OK和NULL）

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