Select * from (
-- productId
select convert(varchar(10), P.ProductId) + '|' + --as PRODUCT_ID,
'<strong>'+brandname+' '+subbrandname+'</strong><br/> '+shortname + '|' + -- as TITLE,
(CASE WHEN EXISTS (
SELECT 1 FROM Product_Navigation_Mapping pnm (NOLOCK)
INNER JOIN Product_Navigation pn1 (NOLOCK) ON pn1.CatalogId = @CatalogId AND pn1.NavigationName = 'Sexual Wellness' AND pn1.ParentId = 0
LEFT JOIN Product_Navigation pn2 (NOLOCK) ON pn2.CatalogId = @CatalogId AND pn2.ParentId = pn1.NavigationId AND pn2.ParentId > 0
WHERE pnm.ProductId = P.ProductId AND (pn1.NavigationId = pnm.NavigationId OR pn2.NavigationId = pnm.NavigationId )
) THEN ''
ELSE shortDesc END) + '|' + -- as SHORT_DESCRIPTION,
(CASE WHEN EXISTS (
SELECT 1 FROM Product_Navigation_Mapping pnm (NOLOCK)
INNER JOIN Product_Navigation pn1 (NOLOCK) ON pn1.CatalogId = @CatalogId AND pn1.NavigationName = 'Sexual Wellness' AND pn1.ParentId = 0
LEFT JOIN Product_Navigation pn2 (NOLOCK) ON pn2.CatalogId = @CatalogId AND pn2.ParentId = pn1.NavigationId AND pn2.ParentId > 0
WHERE pnm.ProductId = P.ProductId AND (pn1.NavigationId = pnm.NavigationId OR pn2.NavigationId = pnm.NavigationId )
) THEN ''
ELSE replace(replace(convert(varchar(max), dbo.fun_stripHTML(isnull(longdesc,''))), char(10), ''), char(13), '') END ) + '|'
这个怎么改成join的方式,谢谢啦~
-- productId
select convert(varchar(10), P.ProductId) + '|' + --as PRODUCT_ID,
'<strong>'+brandname+' '+subbrandname+'</strong><br/> '+shortname + '|' + -- as TITLE,
(CASE WHEN EXISTS (
SELECT 1 FROM Product_Navigation_Mapping pnm (NOLOCK)
INNER JOIN Product_Navigation pn1 (NOLOCK) ON pn1.CatalogId = @CatalogId AND pn1.NavigationName = 'Sexual Wellness' AND pn1.ParentId = 0
LEFT JOIN Product_Navigation pn2 (NOLOCK) ON pn2.CatalogId = @CatalogId AND pn2.ParentId = pn1.NavigationId AND pn2.ParentId > 0
WHERE pnm.ProductId = P.ProductId AND (pn1.NavigationId = pnm.NavigationId OR pn2.NavigationId = pnm.NavigationId )
) THEN ''
ELSE shortDesc END) + '|' + -- as SHORT_DESCRIPTION,
(CASE WHEN EXISTS (
SELECT 1 FROM Product_Navigation_Mapping pnm (NOLOCK)
INNER JOIN Product_Navigation pn1 (NOLOCK) ON pn1.CatalogId = @CatalogId AND pn1.NavigationName = 'Sexual Wellness' AND pn1.ParentId = 0
LEFT JOIN Product_Navigation pn2 (NOLOCK) ON pn2.CatalogId = @CatalogId AND pn2.ParentId = pn1.NavigationId AND pn2.ParentId > 0
WHERE pnm.ProductId = P.ProductId AND (pn1.NavigationId = pnm.NavigationId OR pn2.NavigationId = pnm.NavigationId )
) THEN ''
ELSE replace(replace(convert(varchar(max), dbo.fun_stripHTML(isnull(longdesc,''))), char(10), ''), char(13), '') END ) + '|'
这个怎么改成join的方式,谢谢啦~
brandname+' '+subbrandname