求助PostgreSQL下指定城市可造飞船的单艘成本SQL实现

需求拆解 #

核心要解决两个问题：

• 筛选指定城市可生产的飞船：即该飞船的所有必需零件，该城市均有产出记录
• 计算单艘总成本：对每个零件按公式(100 - stage) * cost / 100计算实际成本，再求和得到单艘飞船的总成本

完整SQL代码 #

WITH required_parts_count AS (
    -- 统计每艘飞船所需的零件总数
    SELECT 
        spaceship_id,
        COUNT(*) AS total_parts
    FROM Spaceship_required_part
    GROUP BY spaceship_id
),
town_provided_parts_count AS (
    -- 统计指定城市可为每艘飞船提供的零件数量
    SELECT 
        srp.spaceship_id,
        COUNT(DISTINCT srp.part_id) AS provided_parts
    FROM Spaceship_required_part srp
    JOIN Spaceship_part_in_town spit 
        ON srp.part_id = spit.part_id 
        AND spit.town_id = 'YOUR_TOWN_ID' -- 替换为目标城市ID（数字类型则去掉引号）
    GROUP BY srp.spaceship_id
),
buildable_spaceships AS (
    -- 筛选出所有必需零件都能在指定城市获取的飞船
    SELECT 
        rpc.spaceship_id
    FROM required_parts_count rpc
    JOIN town_provided_parts_count tppc 
        ON rpc.spaceship_id = tppc.spaceship_id
        AND rpc.total_parts = tppc.provided_parts
),
individual_part_costs AS (
    -- 计算指定城市每个零件的实际成本
    SELECT 
        spit.part_id,
        (100 - spit.stage) * sp.cost / 100 AS actual_cost
    FROM Spaceship_part_in_town spit
    JOIN Spaceship_part sp ON spit.part_id = sp.part_id
    WHERE spit.town_id = 'YOUR_TOWN_ID' -- 同样替换为目标城市ID
)
-- 汇总单艘飞船的总成本
SELECT 
    bs.spaceship_id,
    SUM(ipc.actual_cost) AS single_ship_total_cost
FROM buildable_spaceships bs
JOIN Spaceship_required_part srp ON bs.spaceship_id = srp.spaceship_id
JOIN individual_part_costs ipc ON srp.part_id = ipc.part_id
GROUP BY bs.spaceship_id
ORDER BY bs.spaceship_id;

代码说明 #

1. required_parts_count：统计每艘飞船需要的零件种类数，作为后续生产条件校验的基准
2. town_provided_parts_count：统计指定城市能为每艘飞船提供的零件种类数，和基准数对比判断是否满足生产要求
3. buildable_spaceships：得到所有符合生产条件的飞船ID列表
4. individual_part_costs：根据给定公式计算指定城市每个可用零件的实际成本
5. 最后关联所有表，对每艘可生产飞船的零件成本求和，得到单艘总成本

注意事项 #

• 务必将代码中的'YOUR_TOWN_ID'替换为实际的城市标识，如果是数字类型（比如INT），直接去掉引号即可
• 如果需要显示飞船名称，可以额外关联飞船信息表（假设存在Spaceship表），在SELECT和GROUP BY中加入对应的字段即可
• 确保各表的关联字段（part_id、spaceship_id、town_id）数据类型一致，避免关联失败

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