sql - 如何在CROSS APPLY中处理空行[SQL Server]

我在Stored Procedure下面-

ALTER PROCEDURE [dbo].[Optimized_GetArticlePostAMP]
(
 @PostID int
)

AS
BEGIN
SET NOCOUNT ON;
SET STATISTICS TIME ON

DECLARE @SectionId int ,@datediff int
DECLARE @postdate datetime

 SELECT P.PostId, P.SectionID, P.PostName,MP.MetaTitle,P.Postdate,P.PostAuthor,P.IsApproved,
 MP.Metadescription, MP.Metakeywords,ISNULL(MP.IsRobotsMetaTag,0) as IsRobotsMetaTag,p.TotalViews, P.Subject, P.FormattedBody,
 MV.Isvideo,MV.VideoCode,MV.VideoCaption, A.DrComment,A.SpanishURL, PS.RedirectUrl, Isnull(PS.IsRedirect,0) as IsRedirect,
 ISNULL(A.CommentedBy,38633) as CommentedBy ,MP.Canonical as Canonical,ISNULL(MP.RRpopUP ,0) as RRpopUP,ISNULL(A.PrevPost,0) as PreviousPostId,
 ISNULL(A.NextPost,0) as NextPostId,PS.StatusId ,dbo.[mercola_GetCommentCountForPost](@PostId) as TotalReplies, isnull(PA.[FileName],'') as FileName,
 PRD.StoryImage, PRD.StoryContent, ISNULL(NULLIF(prd.ALT, ''), P.Subject) AS ALT, ISNULL(PR.ReferenceData,'')as ReferenceData,
 MH.LastModifiedDate,
 CASE WHEN CHARINDEX('<p><strong>By', FormattedBody, -1)=1 THEN LTRIM(SUBSTRING(REPLACE(CAST(FormattedBody as varchar(max)),'<p><strong>By ',''),0,CHARINDEX('<',REPLACE(cast(FormattedBody as varchar(max)),'<p><strong>By ',''))))
 ELSE 'Dr.Mercola' END as Name
 FROM cs_posts P
 LEFT JOIN Mercola_NewsletterDetails A on (P.Postid = A.postid)
 LEFT JOIN Mercola_PostStatus PS on (PS.postid=p.postid)
 LEFT JOIN Mercola_PostMetatags MP on(P.postid=MP.Postid)
 LEFT JOIN Mercola_postVideo MV  on(P.postid=MV.Postid)
 LEFT JOIN CS_PostAttachments PA on P.PostId=PA.PostId AND PA.contenttype LIKE 'audio/mpeg' AND PA.FILENAME LIKE '%.mp3' AND PA.isremote = 1
 LEFT JOIN Mercola_PostRelatedData PRD on P.PostId=PRD.PostId
 LEFT JOIN Mercola_PostReferences PR on P.PostId=PR.PostId
 CROSS APPLY (select top 1 LastModifiedDate from Mercola_ArticleModifiedHistory where Mercola_ArticleModifiedHistory.Postid = P.postid order by LastModifiedDate desc)MH
 where P.Postid = @Postid

现在，当我用下面的execute对上面的SP进行PostID时-
--[Mercola_Optimized_GetArticlePostAMP] 732490我得到以下数据，即expected。因此，cross apply内部的查询具有上述postID的数据。



但是现在，当我用下面的不同execute对同一个SP进行PostID编码时-

我在--[Mercola_Optimized_GetArticlePostAMP] 40702下面得到。由于empty data [rows]内部的查询不具有上述cross apply Infact的数据，其他postID则具有数据。



以上情况的预期结果-
返回数据并为joins分配默认值。我该怎么做？

最佳答案

使用OUTER APPLY而不是CROSS APPLY
要覆盖NULL，请使用ISNULL(MH.LastModifiedDate, @DefaultValue) as LastModifiedDate

关于sql - 如何在CROSS APPLY中处理空行[SQL Server]，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37988189/