如何从invoices表查询每位患者最新账单日期及首次就诊对应门店

前提说明 #

现有invoices表字段如下：

• id：账单唯一ID
• date：账单日期
• branch_id：门店ID
• patient_id：患者ID

现有测试数据：

你现有SQL存在语法错误：你使用GROUP BY patients.id，但表名为invoices，患者ID字段为patient_id，需要先修正这个问题。

最终SQL方案 #

方案1：支持窗口函数的数据库（MySQL8.0+、PostgreSQL、Oracle等） #

优先推荐该写法，执行效率更高：

SELECT 
  id,
  date AS first_visit_date,
  branch_id AS first_visit_branch_id,
  patient_id,
  latest_invoice_date
FROM (
  SELECT 
    *,
    -- 取同患者最新账单日期
    MAX(date) OVER (PARTITION BY patient_id) AS latest_invoice_date,
    -- 同患者按账单日期升序排序，第一条就是首次到店记录
    ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY date ASC) AS rn
  FROM invoices
) t
WHERE rn = 1;

方案2：兼容低版本数据库（如MySQL5.x） #

如果你的数据库不支持窗口函数，可以用子查询关联实现：

SELECT 
  i.id,
  i.date AS first_visit_date,
  i.branch_id AS first_visit_branch_id,
  i.patient_id,
  l.latest_invoice_date
FROM invoices i
INNER JOIN (
  -- 先聚合拿到每个患者的首次、最晚账单日期
  SELECT 
    patient_id,
    MIN(date) AS first_visit_date,
    MAX(date) AS latest_invoice_date
  FROM invoices
  GROUP BY patient_id
) l ON i.patient_id = l.patient_id AND i.date = l.first_visit_date;

结果说明 #

以上SQL执行后，会返回每个患者一行数据，包含首次到店的账单ID、首次到店日期、首次到店门店ID、患者ID，以及该患者的最新账单日期，完全符合需求。如果你需要返回最新账单的全量信息同时携带首次到店门店信息，仅需要把窗口函数的排序规则调整为ORDER BY date DESC即可。

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