时间戳

首页 > 解决方案 > 根据其在第一个表中的存在，将来自另一个表的数据附加到组

sql - 根据其在第一个表中的存在，将来自另一个表的数据附加到组

问题描述

我有一张这样的桌子

Project       Project Name  Stage
----------------------------------------
P123           Project1      L1
P123           Project1      L2
P123           Project1      L1
P123           Project1      Phase1

P452           Project2      L1
P452           Project2      L2
P452           Project2      START
P452           Project2      L3

P432           Project3      L1
P432           Project3      L2
P432           Project3      Phase1
P432           Project3      Phase2

我有另一个包含这些数据的表

PhaseCode       PhaseName
------------------------------
Phase1          Select
Phase2          Develop
Phase3          Deploy
Phase4          Close

根据第二张表，我需要找出每个项目代码缺少哪些阶段

所以我需要得到这样的结果

Project       Project Name  Stage     Phase Name      Status
----------------------------------------
P123           Project1      L1
P123           Project1      L2
P123           Project1      L1
P123           Project1      Phase1      Select       Available
P123           Project1      Phase2      Develop        Missing
P123           Project1      Phase3      Deploy        Missing
P123           Project1      Phase4      Close         Missing

P452           Project2      L1   
P452           Project2      L2
P452           Project2      START
P452           Project2      L3
P452           Project2      Phase1      Select       Missing
P452           Project2      Phase2      Develop        Missing
P452           Project2      Phase3      Deploy        Missing
P452           Project2      Phase4      Close         Missing

P432           Project3      L1
P432           Project3      L2
P432           Project3      Phase1     Select       Available
P432           Project3      Phase2     Develop       Available
P452           Project3      Phase3      Deploy        Missing
P452           Project3      Phase4      Close         Missing

我试过交叉连接它不起作用，因为它为第一个表中的每个项目创建 4 个条目。那么如何实现上述结果检查每个组是否存在Phase？

所以我期望的是从阶段表中附加每组 PCode，但只附加不在第一组中的那些

标签： sqlsql-serverunion

解决方案

尝试这个：

SELECT COALESCE(t1.Project, t2.Project) AS Project
    , COALESCE(t1.[Project Name], t2.[Project Name]) AS [Project Name]
    , COALESCE(t2.Stage, t1.PhaseCode) AS Stage
    , t1.PhaseName
    , CASE 
         WHEN PhaseName IS NULL THEN ''
         ELSE CASE 
                 WHEN t2.Project IS NULL THEN 'Missing' 
                 ELSE 'Available' 
              END 
      END AS Status
FROM (
    SELECT DISTINCT Project, [Project Name], PhaseCode, PhaseName
    FROM Project 
    CROSS JOIN Phase
) t1
FULL OUTER JOIN (
    SELECT Project, Stage, [Project Name]
    FROM Project
) t2 ON t1.Project = t2.Project AND t1.PhaseCode = t2.Stage
ORDER BY COALESCE(t1.Project, t2.Project), Stage

演示在这里

为了更好地理解查询的工作原理，您可以查看派生表的行t1：

Project |   Project Name  | PhaseCode | PhaseName
--------|-----------------|-----------|------------
P123    |  Project1       |  Phase1   | Select
P123    |  Project1       |  Phase2   | Develop
P123    |  Project1       |  Phase3   | Deploy
P123    |  Project1       |  Phase4   | Close

P432    |  Project3       |  Phase1   | Select
P432    |  Project3       |  Phase2   | Develop
P432    |  Project3       |  Phase3   | Deploy
P432    |  Project3       |  Phase4   | Close

P452    |  Project2       |  Phase1   | Select
P452    |  Project2       |  Phase2   | Develop
P452    |  Project2       |  Phase3   | Deploy
P452    |  Project2       |  Phase4   | Close

推荐阅读