实现含项目匹配团队不匹配行的表连接：Left/Outer Join失效求助

源数据表 #

ProjectFundingTable（项目资金分配表） #

WorkDoneTable（团队工作量表） #

期望输出结果 #

实现方案 #

你的需求本质是获取两个表基于**项目（Project）+团队（Team）的全量匹配结果，同时保留仅在其中一张表存在的单边行，这对应SQL中的全外连接（Full Outer Join）**场景。

方案1：支持Full Outer Join的数据库（如SQL Server、PostgreSQL） #

直接使用全外连接即可，通过COALESCE确保项目列始终有值：

SELECT
    COALESCE(a.Project, b.Project) AS Project,
    a.Team AS TeamFromTableA,
    b.Team AS TeamFromTableB,
    a.TeamFundingAllocation,
    b.WordDoneAmount
FROM ProjectFundingTable a
FULL OUTER JOIN WorkDoneTable b
    ON a.Project = b.Project AND a.Team = b.Team
ORDER BY COALESCE(a.Project, b.Project), 
         COALESCE(a.Team, b.Team);

方案2：不支持Full Outer Join的数据库（如MySQL） #

用LEFT JOIN联合RIGHT JOIN的补集来模拟全外连接：

-- 第一步：取ProjectFundingTable的所有行，匹配WorkDoneTable的对应行
SELECT
    a.Project,
    a.Team AS TeamFromTableA,
    b.Team AS TeamFromTableB,
    a.TeamFundingAllocation,
    b.WordDoneAmount
FROM ProjectFundingTable a
LEFT JOIN WorkDoneTable b
    ON a.Project = b.Project AND a.Team = b.Team

UNION

-- 第二步：取WorkDoneTable中没有匹配到ProjectFundingTable的行
SELECT
    b.Project,
    NULL AS TeamFromTableA,
    b.Team AS TeamFromTableB,
    NULL AS TeamFundingAllocation,
    b.WordDoneAmount
FROM WorkDoneTable b
LEFT JOIN ProjectFundingTable a
    ON a.Project = b.Project AND a.Team = b.Team
WHERE a.Project IS NULL

-- 按项目和团队排序，对齐期望结果
ORDER BY Project, COALESCE(TeamFromTableA, TeamFromTableB);

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