获取销售表中销售人员的最近与上一次销售日期SQL查询需求

现有sales表，数据如下：

需要编写SQL查询，得到每个销售人员对应的最近销售日期（recent_sale）和上一次销售日期（last_sale），若该销售人员仅存在一次销售记录，则两个日期相同，结果格式如下：

可以使用窗口函数ROW_NUMBER()对每个销售人员的销售记录按日期倒序排序，再结合聚合函数获取目标日期：

WITH ranked_sales AS (
    SELECT 
        ID_sale,
        sales_person,
        sale_date,
        ROW_NUMBER() OVER (PARTITION BY sales_person ORDER BY STR_TO_DATE(sale_date, '%d/%m/%Y') DESC) AS rn
    FROM sales
),
sales_dates AS (
    SELECT 
        sales_person,
        MAX(CASE WHEN rn = 1 THEN sale_date END) AS recent_sale,
        COALESCE(MAX(CASE WHEN rn = 2 THEN sale_date END), MAX(CASE WHEN rn = 1 THEN sale_date END)) AS last_sale
    FROM ranked_sales
    GROUP BY sales_person
)
SELECT 
    rs.ID_sale,
    rs.sales_person,
    sd.recent_sale,
    sd.last_sale
FROM ranked_sales rs
JOIN sales_dates sd ON rs.sales_person = sd.sales_person
WHERE rs.rn = 1
ORDER BY rs.ID_sale DESC;

代码说明 #

1. ranked_sales CTE：对每个销售人员的销售记录，先将字符串格式的sale_date转换为标准日期类型，再按日期倒序排序并标注行号rn，rn=1即为该销售人员最近的销售记录。
2. sales_dates CTE：按销售人员分组，提取rn=1的日期作为recent_sale；提取rn=2的日期作为last_sale，若不存在第二条记录（即仅一次销售），用COALESCE将last_sale设为与recent_sale相同。
3. 最后关联两个CTE，筛选出每个销售人员最近的销售记录（rn=1），并展示对应的日期信息。

若你的数据库中sale_date本身就是日期类型，可去掉STR_TO_DATE转换，直接用sale_date排序。

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