如何查找creation_date列间隔不足两年的关联发票记录

你需要查找的是和父级发票日期间隔不满2年的子发票记录，可通过以下两种方案实现：

方案1：直接筛选相邻不合规节点 #

通过自连接匹配父子发票，直接过滤间隔不满2年的记录即可，不同数据库的日期计算函数略有差异，示例如下：

SELECT 
  child.id 子发票ID,
  child.creation_date 子发票创建日期,
  parent.id 父发票ID,
  parent.creation_date 父发票创建日期
FROM invoices child
JOIN invoices parent ON child.invoice_id = parent.id
-- MySQL 环境用以下筛选条件
WHERE TIMESTAMPDIFF(YEAR, parent.creation_date, child.creation_date) < 2
-- PostgreSQL 环境替换为
-- WHERE EXTRACT(YEAR FROM AGE(child.creation_date, parent.creation_date)) < 2
-- SQL Server 环境替换为
-- WHERE DATEDIFF(YEAR, parent.creation_date, child.creation_date) < 2

这个查询返回的结果就是所有相邻间隔不满2年的发票对，对应你示例中的invoice_2及其父发票invoice_1的记录会直接命中。

方案2：递归查询完整不合规链路 #

如果你需要输出不合规节点所属的完整关联链路，可使用递归CTE实现全链路遍历：

WITH RECURSIVE invoice_chain AS (
  -- 锚点：查找所有无父节点的根发票
  SELECT 
    id,
    invoice_id parent_id,
    creation_date,
    ARRAY[id] full_chain,
    0 chain_level,
    NULL::DATE parent_creation_date,
    FALSE is_non_compliant
  FROM invoices
  WHERE invoice_id IS NULL
  UNION ALL
  -- 递归关联所有子节点
  SELECT 
    cur.id,
    cur.invoice_id parent_id,
    cur.creation_date,
    pre.full_chain || cur.id full_chain,
    pre.chain_level + 1 chain_level,
    pre.creation_date parent_creation_date,
    EXTRACT(YEAR FROM AGE(cur.creation_date, pre.creation_date)) < 2 is_non_compliant
  FROM invoices cur
  JOIN invoice_chain pre ON cur.invoice_id = pre.id
)
SELECT * FROM invoice_chain WHERE is_non_compliant = TRUE;

该查询返回的结果会携带不合规节点的完整链路路径、所在层级等信息，方便你溯源整组关联发票。

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