Informix查询：筛选恰好两条记录且fechaModificacion间隔≤1年的idPersona

方法一：自连接 + 分组筛选 #

通过自连接配对同一用户的两条记录，同时筛选出记录数恰好为2且日期间隔符合要求的用户：

SELECT p1.idPersona
FROM PARTICIPACION p1
INNER JOIN PARTICIPACION p2 
    ON p1.idPersona = p2.idPersona 
    AND p1.idParticipacion < p2.idParticipacion
WHERE 
    -- 排除修改日期为空的记录，避免无法计算间隔
    p1.fechaModificacion IS NOT NULL 
    AND p2.fechaModificacion IS NOT NULL
    -- 计算两条记录的修改日期间隔不超过1年
    AND (p2.fechaModificacion - p1.fechaModificacion) <= INTERVAL(1) YEAR TO YEAR
GROUP BY p1.idPersona
-- 确保该用户恰好只有两条记录（自连接后仅产生1组配对）
HAVING COUNT(*) = 1

方法二：窗口函数（Informix 12.10+ 支持） #

利用窗口函数统计用户的记录数，并获取下一条记录的修改日期，再筛选符合条件的用户：

WITH persona_records AS (
    SELECT
        idPersona,
        fechaModificacion,
        -- 统计当前用户的总记录数
        COUNT(*) OVER (PARTITION BY idPersona) AS record_count,
        -- 获取同用户下一条记录的修改日期
        LEAD(fechaModificacion) OVER (PARTITION BY idPersona ORDER BY fechaModificacion) AS next_mod_date
    FROM PARTICIPACION
    WHERE fechaModificacion IS NOT NULL
)
SELECT DISTINCT idPersona
FROM persona_records
WHERE 
    -- 仅保留恰好有两条记录的用户
    record_count = 2
    -- 验证两条记录的修改日期间隔不超过1年
    AND (next_mod_date - fechaModificacion) <= INTERVAL(1) YEAR TO YEAR

说明 #

• 两种方法都处理了fechaModificacion为NULL的情况，空值无法计算日期间隔，这类记录会被排除。
• Informix中datetime类型相减会得到interval类型，直接与INTERVAL(1) YEAR TO YEAR比较即可判断间隔是否不超过1年。

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