在子查询中引用外层查询ID计算发票首次付款日期的问题咨询

替换规则 #

你只需要给外层查询的invoices表设置别名，即可在子查询中正确引用外层的invoice_id字段，#INVOICE_REF_ID需要替换为外层表别名对应的字段值。
完整的正确查询语句如下：

SELECT 
  i.invoice_id,
  i.report_date,
  i.status,
  CASE i.status
     WHEN 'PAID' THEN
        (SELECT report_date FROM invoices 
         WHERE invoice_id = i.invoice_id
         AND status = 'PAID' 
         ORDER BY report_date LIMIT 1)
  END AS paid_date
FROM invoices i

更高性能的替代写法 #

如果你使用的数据库支持窗口函数，更推荐用窗口函数实现该需求，可以避免关联子查询逐行扫描的性能消耗：

SELECT 
  invoice_id,
  report_date,
  status,
  CASE WHEN status = 'PAID'
       THEN MIN(CASE WHEN status = 'PAID' THEN report_date END) OVER (PARTITION BY invoice_id)
  END AS paid_date
FROM invoices

该写法按invoice_id分组取对应发票首次变为PAID状态的最小日期，输出结果和你的预期完全一致。

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