求助：MariaDB中关联两表生成指定新列的实现方法

解决方案1：使用窗口函数（推荐，性能更优） #

WITH latest_objectives AS (
    SELECT 
        project_id,
        objective,
        is_core,
        -- 按项目+目标分组，按阶段降序排序，标记最高阶段的记录为1
        ROW_NUMBER() OVER (
            PARTITION BY project_id, objective 
            ORDER BY phase DESC
        ) AS rn
    FROM tableB
)
SELECT 
    a.id AS project_id,
    a.name,
    -- 将objective1的最高阶段is_core值转为列
    MAX(CASE WHEN lo.objective = 'objective1' THEN lo.is_core ELSE 0 END) AS objective1,
    -- 将objective2的最高阶段is_core值转为列
    MAX(CASE WHEN lo.objective = 'objective2' THEN lo.is_core ELSE 0 END) AS objective2
FROM tableA a
-- 左连接确保所有项目都被返回，只关联最高阶段的记录
LEFT JOIN latest_objectives lo ON a.id = lo.project_id AND lo.rn = 1
GROUP BY a.id, a.name;

解决方案2：使用分组子查询（逻辑更直观） #

SELECT 
    a.id AS project_id,
    a.name,
    MAX(CASE WHEN b.objective = 'objective1' THEN b.is_core ELSE 0 END) AS objective1,
    MAX(CASE WHEN b.objective = 'objective2' THEN b.is_core ELSE 0 END) AS objective2
FROM tableA a
LEFT JOIN tableB b ON a.id = b.project_id
-- 关联子查询中得到的每个项目+目标的最高阶段记录
AND (b.project_id, b.objective, b.phase) IN (
    SELECT project_id, objective, MAX(phase) 
    FROM tableB 
    GROUP BY project_id, objective
)
GROUP BY a.id, a.name;

说明 #

• 两种方案都能得到你需要的结果，窗口函数方案在数据量较大时性能更优，因为只需扫描一次表B；
• 如果实际场景中有更多目标（比如objective3、objective4），只需要复制MAX(CASE...)的代码块，替换对应的目标名称即可；
• 使用LEFT JOIN和ELSE 0确保即使某个项目没有对应目标的记录，也会显示0，避免出现NULL值。

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