T-SQL技术问询：计算项目成本增减百分比及解决重复问题

首先我先假设你的数据表结构大概是这样的（如果和实际不符，你可以灵活调整字段名）：

CREATE TABLE project_costs (
    project_id INT,
    date DATE,
    cost DECIMAL(18,2)
);

出现重复数据的常见原因通常有两个：一是同一个project_id在同一日期有多个成本记录，二是查询时没有正确过滤出每个项目仅保留最早和最新日期的成本行。下面给你两种可靠的解决方案：

方法1：使用窗口函数（ROW_NUMBER()）精准筛选 #

这种方法先给每个项目的成本记录按日期排序，标记出最早（日期最小）和最新（日期最大）的行，再聚合计算，能有效避免重复：

WITH ranked_costs AS (
    SELECT 
        project_id,
        cost,
        date,
        -- 按项目分组，日期升序排，标记最早的记录为1
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY date ASC) AS rn_earliest,
        -- 按项目分组，日期降序排，标记最新的记录为1
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY date DESC) AS rn_latest
    FROM project_costs
    -- 如果有无效成本（比如cost为null），可以在这里过滤：WHERE cost IS NOT NULL
)
SELECT 
    project_id,
    -- 获取最早成本
    MAX(CASE WHEN rn_earliest = 1 THEN cost END) AS earliest_cost,
    -- 获取最新成本
    MAX(CASE WHEN rn_latest = 1 THEN cost END) AS latest_cost,
    -- 计算增减百分比（处理除数为0的异常情况）
    CASE 
        WHEN MAX(CASE WHEN rn_earliest = 1 THEN cost END) = 0 THEN NULL
        ELSE ROUND(
            (MAX(CASE WHEN rn_latest = 1 THEN cost END) - MAX(CASE WHEN rn_earliest = 1 THEN cost END)) 
            / MAX(CASE WHEN rn_earliest = 1 THEN cost END) * 100, 
            2
        ) 
    END AS cost_change_percent
FROM ranked_costs
GROUP BY project_id
ORDER BY project_id;

如果你的项目在同一日期有多个成本记录，想要取该日期的平均成本或者总成本，可以先在CTE里聚合日期维度：

WITH daily_costs AS (
    SELECT 
        project_id,
        date,
        SUM(cost) AS daily_total_cost -- 或者用AVG(cost)取平均
    FROM project_costs
    GROUP BY project_id, date
),
ranked_costs AS (
    SELECT 
        project_id,
        daily_total_cost,
        date,
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY date ASC) AS rn_earliest,
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY date DESC) AS rn_latest
    FROM daily_costs
)
SELECT 
    project_id,
    MAX(CASE WHEN rn_earliest = 1 THEN daily_total_cost END) AS earliest_cost,
    MAX(CASE WHEN rn_latest = 1 THEN daily_total_cost END) AS latest_cost,
    CASE 
        WHEN MAX(CASE WHEN rn_earliest = 1 THEN daily_total_cost END) = 0 THEN NULL
        ELSE ROUND(
            (MAX(CASE WHEN rn_latest = 1 THEN daily_total_cost END) - MAX(CASE WHEN rn_earliest = 1 THEN daily_total_cost END)) 
            / MAX(CASE WHEN rn_earliest = 1 THEN daily_total_cost END) * 100, 
            2
        ) 
    END AS cost_change_percent
FROM ranked_costs
GROUP BY project_id
ORDER BY project_id;

方法2：使用聚合子查询获取最早/最新日期 #

这种方法先找到每个项目的最早和最新日期，再关联原表获取对应成本，逻辑更直观：

WITH project_dates AS (
    SELECT 
        project_id,
        MIN(date) AS earliest_date,
        MAX(date) AS latest_date
    FROM project_costs
    GROUP BY project_id
)
SELECT 
    pd.project_id,
    -- 获取最早日期的成本（如果同一天有多条，这里取SUM/AVG，根据需求调整）
    (SELECT SUM(cost) FROM project_costs pc WHERE pc.project_id = pd.project_id AND pc.date = pd.earliest_date) AS earliest_cost,
    -- 获取最新日期的成本
    (SELECT SUM(cost) FROM project_costs pc WHERE pc.project_id = pd.project_id AND pc.date = pd.latest_date) AS latest_cost,
    -- 计算百分比
    CASE 
        WHEN (SELECT SUM(cost) FROM project_costs pc WHERE pc.project_id = pd.project_id AND pc.date = pd.earliest_date) = 0 THEN NULL
        ELSE ROUND(
            (
                (SELECT SUM(cost) FROM project_costs pc WHERE pc.project_id = pd.project_id AND pc.date = pd.latest_date) 
                - (SELECT SUM(cost) FROM project_costs pc WHERE pc.project_id = pd.project_id AND pc.date = pd.earliest_date)
            ) 
            / (SELECT SUM(cost) FROM project_costs pc WHERE pc.project_id = pd.project_id AND pc.date = pd.earliest_date) * 100, 
            2
        ) 
    END AS cost_change_percent
FROM project_dates pd
ORDER BY pd.project_id;

为什么你之前的查询会出现重复数据？ #

大概率是这两个原因：

• 没有对project_id做正确分组，或者分组逻辑错误，导致每个成本记录都被单独返回；
• 同一个项目在最早/最新日期有多个成本记录，没有做聚合（比如SUM/AVG）就直接关联，导致一条项目对应多条结果。

你可以根据自己的实际表结构和业务需求，调整上面的代码～

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