[步骤]
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ISNULL(SubmitTime,GETDATE()+1) DESC ) AS [PageIndex] ,* FROM ( SELECT pd.[Id] ,
pd.PackLength,
pd.PackWidth,
pd.PackHeigth,
pd.GrossWeight,
pd.BillingWeight,
pd.[CustomerID] ,
pd.[ProductName] ,
pd.[ProductArtworkUrl] ,
pd.[ProductThumbnailUrl] ,
pd.[ProductLink] ,
pd.[ProductLink2] ,
pd.[ProductLink3] ,
pd.[ProductLinkUniqueId],
pd.[ProcutClassOne] ,
class1.ClassNameCn ProductClass1Name,
pd.[ProcutClassTwo] ,
pd.[ProcutClassThree] ,
pd.[PlatformClassId],
pd.[PlatformClassName],
pd.[PlatformClassOneId],
pd.[PlatformClassOneNameCn],
pd.[PlatformClassTwoId],
pd.[PlatformClassTwoNameCn],
pd.[PersonType],
pd.SalesMarketCountryEN ,
pd.[SalesMarket] ,
pd.[SalesPlatformId] ,
pd.[SalesPlatformName] ,
pd.[DailyEstimatedSales] ,
pd.[TargetPurchasingPrice] ,
pd.[CostPurchasePrice] ,
pd.[ActualDevelopmentPrice] ,
pd.[ReferencePriceUSD] ,
pd.[ReferencePriceRMB] ,
pd.[CompletionSchedule] ,
pd.[CurrentStatus] ,
pd.[CurrentStatusDescription] ,
pd.[Remarks] ,
pd.[AddTime] ,
pd.[AddAdminId] ,
pd.[AddAdminName] ,
d.[DepartmentName] as AddAdminDepartmentName,
pd.[UpdateTime] ,
pd.[UpdateId] ,
pd.[UpdateName] ,
pd.[FirstLevelAuditId] ,
pd.[FirstLevelAuditId2] ,
pd.[ProductGroupType],
pd.[ProductHighLights],
( class1.ClassNameCn + ' x >>'
+ CASE WHEN pd.ProcutClassTwo = 0 THEN ''
ELSE class2.ClassNameCn + ' x >>'
END + CASE WHEN pd.ProcutClassTwo = 0
AND pd.ProcutClassThree <> 0 THEN ''
WHEN pd.ProcutClassThree = 0 THEN ''
WHEN pd.ProcutClassTwo = pd.ProcutClassThree THEN ''
ELSE class3.ClassNameCn
END ) AS ProductClassName ,
pd.DevelopmentTime ,
pd.DevelopmentId ,
pd.DevelopmentName ,
dev.AddAdminId OperateUser,
dev.AddAdminName OperateUserName,
pd.OverTime ,
pd.DevelopmentStatusRemarks ,
pd.PictureName ,
pd.ApplicationNumber ,
pd.DevelopmentSuccessSku ,
pd.EmergencyDegree ,
pd.DistributionTime ,
pd.IsPhotograph ,
pd.DevelopmentStyle ,
pd.AutoChooseKeyWords ,
pd.AutoChooseKeyWordsCn ,
pd.RejectOptionId,
pd.RejectRemark,
pd.RejectImageUrl,
pip.Id AS IntellectualProperty_Id,
pip.Code AS IntellectualPropertyCode,
pip.SubmitTime AS IntellectualProperty_SubmitTime,
pip.RecoveryTime AS IntellectualProperty_RecoveryTime,
pip.RecoveryAdminName AS IntellectualProperty_RecoveryAdminName,
pip.LastRecoveryTime AS IntellectualProperty_LastRecoveryTime,
pip.LastRecoveryAdminName AS IntellectualProperty_LastRecoveryAdminName,
pip.RecoveryStatus AS IntellectualProperty_RecoveryStatus,
ISNULL(( SELECT TOP 1 conditionallyenabledtypename = ( STUFF((SELECT ',' + conditionallyenabledtypename FROM rb_product_intellectualpropertymanagement_conditionallyenabledtype
WHERE customerid = PD.customerid AND intellectualpropertymanagementid = PIP.ID
FOR XML PATH('')), 1, 1, '') ) FROM rb_product_intellectualpropertymanagement_conditionallyenabledtype
GROUP BY intellectualpropertymanagementid,addtime
ORDER BY addtime DESC
), '') AS conditionallyenabledtypename,
ISNULL(( SELECT TOP 1
Remarks
FROM RB_Product_Development_Status (NOLOCK)
WHERE CustomerID = pd.CustomerID
AND ProductDevelopmentId = pd.Id
AND Status IN ( 'SuccessfulDevelopment', 'DevelopmentFailure',
'FailureOfDevelopment', 'Completed' )
ORDER BY AddTime DESC
), '') AS DevelopmentRemark ,
ISNULL(( SELECT TOP 1
Remarks
FROM RB_Product_Development_Status (NOLOCK)
WHERE CustomerID = pd.CustomerID
AND ProductDevelopmentId = pd.Id
AND Status = pd.CurrentStatus
ORDER BY AddTime DESC
), '') AS StatusRemark ,
ISNULL(( SELECT TOP 1
AddTime
FROM RB_Product_Development_Status (NOLOCK)
WHERE CustomerID = pd.CustomerID
AND ProductDevelopmentId = pd.Id
AND Status = pd.CurrentStatus
ORDER BY AddTime DESC
), '') AS StatusTime ,
(SELECT TOP 1
AddTime
FROM dbo.RB_Product_Development_Status
WHERE CustomerID = pd.CustomerID
AND Status = 'Committed'
AND ProductDevelopmentId = pd.Id
ORDER BY AddTime DESC
) SubmitTime ,
pd.SalesAdminIds,
pd.GatherInfo,pd.ProductNameEn,pd.Sources,pd.SourcesContent,pd.SourcesTxt,pd.PutTypeTxt,pd.ToFbaTime,pd.ExcelAddFields
,pd.FailImgUrl
,pd.FailOptionReason
,pdfail.Reason
FROM dbo.RB_Product_Development pd WITH ( NOLOCK )
LEFT JOIN dbo.RB_Admin a on pd.AddAdminId = a.Id
LEFT JOIN dbo.RB_Department d ON pd.AddAdminDepartmentId = d.Id
LEFT JOIN dbo.RB_Product_Class class1 WITH ( NOLOCK ) ON pd.CustomerID = class1.CustomerID
AND pd.ProcutClassOne = class1.ID
LEFT JOIN dbo.RB_Product_Class class2 WITH ( NOLOCK ) ON class2.CustomerID = pd.CustomerID
AND pd.ProcutClassTwo = class2.ID
LEFT JOIN dbo.RB_Product_Class class3 WITH ( NOLOCK ) ON class3.CustomerID = pd.CustomerID
AND pd.ProcutClassThree = class3.ID
LEFT JOIN (SELECT CustomerID,ProductDevelopmentId,AddAdminId,AddAdminName from RB_Product_Development_Status a (NOLOCK) where AddTime=(SELECT MAX(AddTime) from RB_Product_Development_Status b (NOLOCK) where a.CustomerID=b.CustomerID and a.ProductDevelopmentId=b.ProductDevelopmentId
) GROUP by CustomerID,ProductDevelopmentId,AddAdminId,AddAdminName) dev on pd.CustomerID=dev.CustomerID and pd.ID=dev.ProductDevelopmentId
OUTER APPLY(SELECT TOP 1 i.Id,i.Code,i.SubmitTime,i.RecoveryTime,i.RecoveryAdminName,i.RecoveryStatus,i.LastRecoveryTime,i.LastRecoveryAdminName
FROM RB_Product_IntellectualProperty_Management i WITH ( NOLOCK )
inner join RB_Product_Development_IntellectualProperty pdip WITH ( NOLOCK )
on pdip.ProductDevelopmentId = pd.Id
and pdip.IntellectualPropertyId = i.id ORDER BY Id DESC) pip
left join RB_Product_Development_FailOption pdfail on pdfail.Id=pd.FailOptionId
Where pd.CustomerID =1 AND pd.CurrentStatus IN('ToBeAllocated','HitBackToBeAllocated','Audited','InDevelopment','Distributed','Return','FailureOfDevelopment','SuccessfulDevelopment', 'Allocated', 'Completed')
AND pd.Id in(SELECT DISTINCT ProductDevelopmentId
FROM dbo.RB_Product_Development_Status
WHERE CustomerID = pd.CustomerID
AND Status = 'Committed'
AND AddTime >= '2025-06-28 00:00:01'
AND AddTime <= '2025-7-28 23:59:59'
)
) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 25
[结果]
[期望]
检查所有表补全nolock ，且 FROM rb_product_intellectualpropertymanagement_conditionallyenabledtypeGROUP BY intellectualpropertymanagementid,addtime 该出查询缺少 关联条件 导致 无数次 自循环 ，是否也应该添加 关联 intellectualpropertymanagementid = PIP.ID