如何获取下一条记录列值并计算月份日期差

嘿，我来帮你搞定这个需求！你已经有了按日期倒序排列的职位历史记录，现在想要获取每条记录的下一条更早的记录，并计算两个日期之间的月份差值对吧？

现有结果集 #

原查询语句 #

SELECT DISTINCT designation_id as id, d.title as POST, DATE(dt_datetime) as DATE 
FROM users_history_check u 
INNER JOIN designations d ON d.id = u.designation_id 
WHERE u.id = $userID 
ORDER BY DATE DESC

解决方案：使用窗口函数 + 日期差计算 #

我们可以用LAG()窗口函数来获取每条记录的上一条更早的记录日期，然后用日期函数计算月份差。不同数据库的日期差函数略有不同，下面给出常见数据库的实现：

1. MySQL 版本

SELECT
  designation_id as id,
  d.title as POST,
  DATE(u.dt_datetime) as CURRENT_DATE,
  LAG(DATE(u.dt_datetime)) OVER (ORDER BY DATE(u.dt_datetime) DESC) as PREVIOUS_DATE,
  -- 计算月份差：PERIOD_DIFF(当前年月, 上一条年月)
  PERIOD_DIFF(
    DATE_FORMAT(DATE(u.dt_datetime), '%Y%m'),
    DATE_FORMAT(LAG(DATE(u.dt_datetime)) OVER (ORDER BY DATE(u.dt_datetime) DESC), '%Y%m')
  ) as MONTH_DIFF
FROM users_history_check u 
INNER JOIN designations d ON d.id = u.designation_id 
WHERE u.id = $userID 
ORDER BY CURRENT_DATE DESC

2. PostgreSQL 版本

SELECT
  designation_id as id,
  d.title as POST,
  DATE(u.dt_datetime) as CURRENT_DATE,
  LAG(DATE(u.dt_datetime)) OVER (ORDER BY DATE(u.dt_datetime) DESC) as PREVIOUS_DATE,
  -- 计算月份差：提取年份差*12 + 月份差
  EXTRACT(YEAR FROM age(DATE(u.dt_datetime), LAG(DATE(u.dt_datetime)) OVER (ORDER BY DATE(u.dt_datetime) DESC))) * 12 +
  EXTRACT(MONTH FROM age(DATE(u.dt_datetime), LAG(DATE(u.dt_datetime)) OVER (ORDER BY DATE(u.dt_datetime) DESC))) as MONTH_DIFF
FROM users_history_check u 
INNER JOIN designations d ON d.id = u.designation_id 
WHERE u.id = $userID 
ORDER BY CURRENT_DATE DESC

3. SQL Server 版本

SELECT
  designation_id as id,
  d.title as POST,
  CAST(u.dt_datetime AS DATE) as CURRENT_DATE,
  LAG(CAST(u.dt_datetime AS DATE)) OVER (ORDER BY CAST(u.dt_datetime AS DATE) DESC) as PREVIOUS_DATE,
  -- 计算月份差：DATEDIFF直接取月份差
  DATEDIFF(MONTH, LAG(CAST(u.dt_datetime AS DATE)) OVER (ORDER BY CAST(u.dt_datetime AS DATE) DESC), CAST(u.dt_datetime AS DATE)) as MONTH_DIFF
FROM users_history_check u 
INNER JOIN designations d ON d.id = u.designation_id 
WHERE u.id = $userID 
ORDER BY CURRENT_DATE DESC

预期结果 #

执行后你会得到类似这样的结果：

注：最早的那条记录没有上一条，所以PREVIOUS_DATE和MONTH_DIFF会是NULL，你可以根据需求用COALESCE处理成0或者其他值。

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