如何用MySQL查询按CNPJ分组、END_DATE排序的上一条TARGET_CNPJ

数据老陈

2026-9-25

需求可实现性说明

该需求完全可以通过MySQL查询实现，根据你使用的MySQL版本不同有两种落地方案：

方案1：MySQL 8.0+ 版本（推荐，性能最优）

直接使用窗口函数LAG()即可实现，核心排序逻辑通过IF(END_DATE IS NULL,1,0)控制空值排在末尾，符合你的需求。
示例查询语句：

SELECT 
  CNPJ,
  TARGET_CNPJ,
  END_DATE,
  LAG(TARGET_CNPJ, 1, '无前置TARGET_CNPJ') OVER (
    PARTITION BY CNPJ 
    ORDER BY IF(END_DATE IS NULL, 1, 0) ASC, END_DATE ASC
  ) AS LAST_CNPJ
FROM 你的实际表名;

方案2：MySQL 5.x 及更低版本（无窗口函数支持）

可以通过用户变量生成排序序号后自关联实现，示例查询语句：

SELECT 
  t1.CNPJ,
  t1.TARGET_CNPJ,
  t1.END_DATE,
  IFNULL(t2.TARGET_CNPJ, '无前置TARGET_CNPJ') AS LAST_CNPJ
FROM (
    SELECT 
      *,
      @rn := IF(@cur_cnpj = CNPJ, @rn + 1, 1) AS row_num,
      @cur_cnpj := CNPJ
    FROM 你的实际表名
    CROSS JOIN (SELECT @rn := 0, @cur_cnpj := '') AS init_vars
    ORDER BY CNPJ, IF(END_DATE IS NULL, 1, 0) ASC, END_DATE ASC
) AS t1
LEFT JOIN (
    SELECT 
      *,
      @rn2 := IF(@cur_cnpj2 = CNPJ, @rn2 + 1, 1) AS row_num,
      @cur_cnpj2 := CNPJ
    FROM 你的实际表名
    CROSS JOIN (SELECT @rn2 := 0, @cur_cnpj2 := '') AS init_vars
    ORDER BY CNPJ, IF(END_DATE IS NULL, 1, 0) ASC, END_DATE ASC
) AS t2 ON t1.CNPJ = t2.CNPJ AND t1.row_num = t2.row_num + 1;