[步骤]
a.SKU,
a.IsGroup,
a.ProductGroupSKU AS [母体],
CASE
WHEN b.HasImitationCountry = 0 THEN '非仿牌'
ELSE '仿牌'
END AS [仿牌侵权],
b.NoSalePlatform AS [平台禁售],
a.OnlineStatus AS [状态],
CASE
WHEN b.RiskLevelType = 0
OR b.RiskLevelType = '' THEN rv.RiskLevel
ELSE b.RiskLevelType
END AS '风险等级',
CASE b.HasPattern
WHEN 1 THEN '单图案'
WHEN 4 THEN '多图案'
ELSE '其他'
END AS [图案类型],
CASE b.IsAmazonUsWhiteList
WHEN 1 THEN '白名单'
ELSE '其他'
END AS [是否白名单]
FROM RB_Product a
LEFT JOIN RB_Product_Admin b
ON a.SKU = b.sku
AND a.CustomerID = b.CustomerID
LEFT JOIN (SELECT sku,
Sum(goodnum) AS 库存,
customerid
FROM RB_WareHouse_Product
WHERE CustomerID = 1
AND WareHouseID IN ( 333, 5312, 2 )
GROUP BY sku,
customerid) wp
ON wp.sku = a.SKU
AND wp.customerid = a.customerid
LEFT JOIN RB_Product_Risklevel_PlatformCountry rv
ON a.SKU = rv.SKU
LEFT JOIN RB_Product_PhotoTask y
ON y.SKU = a.SKU
LEFT JOIN (SELECT sku
FROM dbo.RB_Product_RiskLevelPlatform WITH (NOLOCK)
WHERE CustomerID = 1
AND ( ( PlatformId = 1
AND CountryShorthand = '' )
OR PlatformId = 10
OR ( PlatformId = 1
AND CountryShorthand NOT LIKE '%US%' ) )
UNION
SELECT sku
FROM dbo.RB_Product_NoSalePlatform WITH (NOLOCK)
WHERE CustomerID = 1
AND ( ( PlatformId = 1
AND CountryShorthand = '' )
OR PlatformId = 10
OR ( PlatformId = 1
AND CountryShorthand NOT LIKE '%US%' ) )) o
ON a.SKU = o.sku
WHERE a.CustomerID = 1
AND b.CheckStatus = 1
AND a.ProductState = 0
AND a.OnlineStatus = 1
AND b.HasImitationCountry = 0
AND Isnull(a.IsGroup, 0) = 0
AND a.DeleteStatus = 0
AND wp.库存 > 0;
[结果]
[期望]
同步数据，不要包含过多的业务逻辑，补全nolock
