为每家公司在最后记录后新增季度行并生成write_off字段的SQL查询需求

开发者阿泽

2026-7-6

解决方法

要实现给每家公司在最新记录的下一季度新增一行的需求，不能用LEAD()函数——因为LEAD()只是查询现有数据中同行列的下一行值，无法生成原本不存在的新行。正确的思路是用UNION ALL把原表数据和我们生成的新增行拼接起来，步骤如下：

核心逻辑

先查询原表所有数据，给每行加上write_off = false
单独生成每家公司的新增行：
- 找出每家公司的最新季度日期
- 基于该日期计算下一个季度末的日期（原日期都是季度末，加3个月/1个季度自然得到下一个季度末）
- 设置value = null，write_off = true
把两部分结果合并，按公司和日期排序

不同SQL方言的实现代码

假设原表名为quarterly_data，以下是主流数据库的写法：

PostgreSQL

-- 原表数据，标记为非核销行
SELECT date, company, value, false AS write_off
FROM quarterly_data
UNION ALL
-- 生成每家公司的下一季度核销行
SELECT 
    (MAX(date) + INTERVAL '3 months')::date AS date,
    company,
    NULL AS value,
    true AS write_off
FROM quarterly_data
GROUP BY company
-- 按公司和日期排序，保证结果顺序正确
ORDER BY company, date;

MySQL

SELECT date, company, value, false AS write_off
FROM quarterly_data
UNION ALL
SELECT 
    DATE_ADD(MAX(date), INTERVAL 3 MONTH) AS date,
    company,
    NULL AS value,
    true AS write_off
FROM quarterly_data
GROUP BY company
ORDER BY company, date;

SQL Server

SELECT date, company, value, CAST(0 AS BIT) AS write_off
FROM quarterly_data
UNION ALL
SELECT 
    DATEADD(QUARTER, 1, MAX(date)) AS date,
    company,
    NULL AS value,
    CAST(1 AS BIT) AS write_off
FROM quarterly_data
GROUP BY company
ORDER BY company, date;