SQL Server 2012 with table as 如果一个表为空，则不返回任何内容

Question

我在两个表上使用With As语句，并从每个表中返回几列。每个查询本身都可以正常工作。当数据位于满足 Where 子句的两个表中时，它工作正常。但是，当一个表没有满足 where 子句的内容而另一个表时，我得到一个空返回。这似乎是一个内部 where 子句不会影响另一个表，但我看不出我做错了什么。

;WITH Issue AS
(
    SELECT  
        I.[Title] as [Issue],
        dbo.udf_StripHTML([Discussion]) as Discussion
    FROM 
        [PSMADO].[dbo].[MSP_EpmProject_UserView] AS P
    JOIN
        [psmado].[dbo].[MSP_WssIssue_OlapView] AS I ON I.[projectUID] = P.[projectUID]
    WHERE
        I.[status] LIKE '%Active%'
        AND I.[Category] LIKE '%Factors%'
        AND p.[Workstream] LIKE 'PSO'
), Risk AS
(
    SELECT 
        I.[Title] as [Risk], 
        dbo.udf_StripHTML([MitigationPlan]) AS Mitigation
    FROM 
        [PSMADO].[dbo].[MSP_EpmProject_UserView] AS P
    JOIN
        [psmado].[dbo].[MSP_WssRisk_OlapView] AS I ON I.[projectUID] = P.[projectUID]
    WHERE
        I.[status] LIKE '%Active%'
        AND I.[Category] LIKE '%Factors%'
        AND p.[Workstream] LIKE 'PSO'
)
SELECT *
FROM Issue, Risk

Answer 1

我认为你需要的是LEFT JOIN，所有risk和issue条件都将被置于join clause.

SELECT 
    P.projectUID, 
    risk.[Title] as [Risk], 
    dbo.udf_StripHTML(risk.[MitigationPlan]) AS Mitigation,
    I.[Title] as [Issue],
    dbo.udf_StripHTML(I.[Discussion]) as Discussion
FROM 
    [PSMADO].[dbo].[MSP_EpmProject_UserView]  P
LEFT JOIN [psmado].[dbo].[MSP_WssRisk_OlapView] risk 
    ON (risk.[projectUID] = P.[projectUID] AND risk.[status] LIKE '%Active%' 
         AND risk.[Category] LIKE '%Factors%')
LEFT JOIN
    [psmado].[dbo].[MSP_WssIssue_OlapView]  I 
    ON (I.[projectUID] = P.[projectUID]  AND I.[status] LIKE '%Active%' 
        AND I.[Category] LIKE '%Factors%')
WHERE
    p.[Workstream] LIKE 'PSO'

我注意到p.[Workstream] LIKE 'PSO'等于p.[Workstream] = 'PSO'.

也许你需要p.[Workstream] LIKE '%PSO%'