c# - 使用空值连接表

Question

当我尝试使用单个计划获取一个数据时，所有具有空值的结果都会出现，但是当我选择多个计划时，空值就会消失。我想显示空值。怎么了？

SELECT SS.ID
       ,PS.ProductID
       ,PS.Parameters
       ,PS.Specification
       ,ISNULL (RD.Result,'N/A') as Result
       ,SS.DFA
FROM ProductSpecification as PS 
LEFT JOIN (SELECT SR.SpecsID,SR.ID FROM StabilityResult as SR JOIN ProductSpecification as S ON S.ID = SR.SpecsID WHERE S.ProductID = 1757) as R ON R.SpecsID = PS.ID OR (R.SpecsID is NULL AND PS.ID is NULL) 
LEFT JOIN (SELECT D.ResultID,D.ScheduleID,D.Result FROM StabilityResultDetails as D WHERE D.ScheduleID = 131) as RD ON RD.ResultID = R.ID OR (RD.ResultID IS NULL AND R.ID IS NULL)
LEFT JOIN (SELECT S.ID,S.DFA FROM StabilitySched as S WHERE S.ID=131 ) as SS ON SS.ID = RD.ScheduleID OR (SS.ID IS NULL AND RD.ScheduleID IS NULL) 
WHERE PS.ProductID=1757 
GROUP BY RD.ScheduleID,PS.ProductID,PS.Parameters, PS.Specification, RD.Result,SS.DFA,SS.ID

Answer 1

由于 StabilitySched 是您选择获取其他详细信息的主表，您需要从该表开始并左连接其他表以获得所需的结果。希望下面的查询会产生预期的结果，

SELECT SS.ID,R.ProductID,R.Parameters,R.Specification,
     ISNULL (RD.Result,'N/A') as Result,SS.DFA
FROM StabilitySched SS
LEFT JOIN
(
    SELECT D.ResultID,D.ScheduleID,D.Result FROM StabilityResultDetails
) RD ON SS.ID = RD.ScheduleID
LEFT JOIN 
(
   SELECT SR.SpecsID,SR.ID,PS.ProductID,PS.Parameters,PS.Specification 
   FROM StabilityResult SR 
   JOIN ProductSpecification PS ON SR.SpecsID = PS.ID
   WHERE PS.ProductID = 1757
) as R ON RD.ResultID = R.ID;

这里我们不需要分组依据，因为我们没有进行任何聚合操作，例如计数/求和，...并且不需要像默认情况下那样在连接中指定 is null 条件。

希望对您有所帮助!