SELECT * FROM( SELECT ROW_NUMBER() OVER (ORDER BY r.UpdateTime desc) tempRowNum,h.BizID,h.OrgID FROM dbo.ToPlanTargetValueHis h INNER JOIN dbo.ToPlanAdjustRecord r ON h.BizID=r.ID WHERE h.BizID<>'import' AND r.AdjustAmount<>-1 AND DATEDIFF(YEAR,'',r.BizDate)=0 AND h.OrgID='0972d31c170d41b2b778d6b2ab203b7e' AND DATEDIFF(DAY, '', r.BizDate) >= 0 AND DATEDIFF(DAY, '', r.BizDate) <= 0 GROUP BY h.BizID,h.OrgID,r.UpdateTime) a LEFT JOIN (SELECT r.ID,( SELECT s.ShortName+',' FROM ToPlanTargetValueHis h LEFT JOIN dbo.SysOrganization s ON h.OrgID=s.ID WHERE h.BizID=r.ID AND h.AdjustType='in' ORDER BY h.UpdateTime DESC FOR XML PATH('')) InOrgName, (SELECT f.Name+',' FROM ToPlanTargetValueHis h LEFT JOIN dbo.ToFinanceAccount f ON h.TargetID=f.ID WHERE h.AdjustType='in' AND h.BizID=r.ID ORDER BY h.UpdateTime DESC FOR XML PATH('')) InTargetName, (SELECT s.ShortName+',' FROM ToPlanTargetValueHis h LEFT JOIN dbo.SysOrganization s ON h.OrgID=s.ID WHERE h.BizID=r.ID AND h.AdjustType='out' ORDER BY h.UpdateTime DESC FOR XML PATH('')) OutOrgName, (SELECT f.Name FROM ToPlanTargetValueHis h LEFT JOIN dbo.ToFinanceAccount f ON h.TargetID=f.ID WHERE h.AdjustType='out' AND h.BizID=r.ID ORDER BY h.UpdateTime desc FOR XML PATH('')) OutTargetName, r.BizType,(SELECT TOP 1 h.IsAdjustYear FROM ToPlanTargetValueHis h WHERE h.BizID =r.ID ORDER BY h.UpdateTime DESC) IsAdjustYear, r.UpdateTime AS BizDate,r.AdjustStatus,u.EmpName UserName ,(SELECT CONVERT(NVARCHAR(1000),h.TargetValue)+',' FROM dbo.ToPlanTargetValueHis h WHERE h.AdjustType IN('in','out') AND h.BizID=r.ID ORDER BY h.UpdateTime DESC FOR XML PATH('')) AdjustAmount, (SELECT CONVERT(NVARCHAR(1000),h.AfterAdjust)+',' FROM toplantargetvaluehis h WHERE h.AdjustType IN('in','out') AND h.BizID=r.ID ORDER BY h.UpdateTime desc FOR XML PATH('')) AfterAdjust, (SELECT COUNT(1) FROM dbo.ToPlanTargetValue WHERE BizID=r.ID) IsEnable,r.ProcInstID,w.Status as WFStatus,Remark FROM dbo.ToPlanAdjustRecord r INNER JOIN dbo.SysUser u ON r.UpdateUser =u.ID LEFT JOIN dbo.WF_ProcInst AS w ON w.ProcInstID=r.ProcInstID) b ON a.BizID=b.ID WHERE a.tempRowNum>0 AND a.tempRowNum<=10 ORDER BY a.tempRowNum