获取用户各申请记录的上一次申请日期的SQL实现需求

针对需求，使用SQL窗口函数LAG()可以高效实现每条记录对应上一次申请日期的查询，以下是各主流数据库的实现代码：

MySQL 实现 #

SELECT
    Person,
    `Applic ID`,
    DATE_FORMAT(`Applic Date`, '%d/%m/%y') AS `Applic Date`,
    COALESCE(DATE_FORMAT(LAG(`Applic Date`) OVER (PARTITION BY Person ORDER BY `Applic Date`), '%d/%m/%y'), '-') AS `Prev Applic Date`
FROM
    your_table_name
ORDER BY
    `Applic Date`, Person;

SQL Server 实现 #

SELECT
    Person,
    [Applic ID],
    FORMAT([Applic Date], 'dd/MM/yy') AS [Applic Date],
    COALESCE(FORMAT(LAG([Applic Date]) OVER (PARTITION BY Person ORDER BY [Applic Date]), 'dd/MM/yy'), '-') AS [Prev Applic Date]
FROM
    your_table_name
ORDER BY
    [Applic Date], Person;

Oracle 实现 #

SELECT
    Person,
    "Applic ID",
    TO_CHAR("Applic Date", 'DD/MM/RR') AS "Applic Date",
    COALESCE(TO_CHAR(LAG("Applic Date") OVER (PARTITION BY Person ORDER BY "Applic Date"), 'DD/MM/RR'), '-') AS "Prev Applic Date"
FROM
    your_table_name
ORDER BY
    "Applic Date", Person;

代码说明 #

• LAG() OVER (PARTITION BY Person ORDER BY Applic Date)：按用户分组，按申请日期排序，自动获取当前记录的上一条申请日期
• 日期格式化函数：根据数据库类型选择对应函数，将日期转为DD/MM/YY格式
• COALESCE()：当用户没有上一次申请（即第一条记录）时，将空值替换为'-'

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