基于另一列值从同一列查询日期的SQL实现方案

需求梳理 #

• 任务表（TASK）：[Task Key]为主键，[Task Type]用于同类任务分组，包含[Task Name]、[Task Date]字段
• 类别表（CATEGORY）：通过[Task Key]关联，标记任务类型为Current、Baseline或Snapshot
• 目标：生成新表，为每个任务新增基准日期列，即对应同[Task Type]下Baseline类型任务的[Task Date]

实现思路 #

通过关联两张表后，结合窗口函数的条件聚合，按[Task Type]分组提取组内Baseline任务的日期，作为该组所有任务的基准日期。

基础实现SQL代码 #

SELECT
    ta.[Task Key],
    ta.[Task Type],
    ta.[Task Name],
    ta.[Task Date],
    ca.[Category Type], -- 保留类别类型方便验证结果
    -- 按任务类型分组，提取组内Baseline类型的任务日期作为基准日期
    MAX(CASE WHEN ca.[Category Type] = 'Baseline' THEN ta.[Task Date] END) OVER (PARTITION BY ta.[Task Type]) AS [基准日期]
INTO 新任务表 -- 替换为你需要创建的新表名称
FROM
    TASK ta
LEFT JOIN
    CATEGORY ca
ON
    ta.[Task Key] = ca.[Task Key]

代码说明 #

• PARTITION BY ta.[Task Type]：限定仅在相同任务类型的组内查找基准日期
• MAX(CASE WHEN ca.[Category Type] = 'Baseline' THEN ta.[Task Date] END)：筛选组内Baseline类型的任务日期，用MAX（或MIN，若同组仅一个Baseline）提取该值，作为组内所有任务的基准日期
• INTO 新任务表：直接将查询结果写入新表，若需覆盖已有表可先执行DROP TABLE IF EXISTS 新任务表

多Baseline场景处理 #

如果同个[Task Type]下存在多个Baseline任务，可通过窗口函数筛选出指定的基准日期（例如最早/最晚的）：

WITH BaselineTasks AS (
    SELECT
        [Task Type],
        [Task Date]
    FROM (
        SELECT
            ta.[Task Type],
            ta.[Task Date],
            -- 按日期排序取最早的Baseline，取最晚则改为ORDER BY ta.[Task Date] DESC
            ROW_NUMBER() OVER (PARTITION BY ta.[Task Type] ORDER BY ta.[Task Date]) AS rn
        FROM TASK ta
        JOIN CATEGORY ca ON ta.[Task Key] = ca.[Task Key]
        WHERE ca.[Category Type] = 'Baseline'
    ) t
    WHERE rn = 1
)
SELECT
    ta.[Task Key],
    ta.[Task Type],
    ta.[Task Name],
    ta.[Task Date],
    ca.[Category Type],
    bt.[Task Date] AS [基准日期]
INTO 新任务表
FROM TASK ta
LEFT JOIN CATEGORY ca ON ta.[Task Key] = ca.[Task Key]
LEFT JOIN BaselineTasks bt ON ta.[Task Type] = bt.[Task Type]

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