用空值连接表

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;

这里我们不需要group by，因为我们没有做任何聚合操作，如count/sum，…并且不需要在连接中指定null条件，因为它默认会这样做。

希望有帮助!