多个类似于SQL Server中的从句

Question

我想构建一个带有case语句的多个sql查询。这是我的SQL查询。案例语句用于确保，如果请求来自网站，则该请求是从网站上使用的，则案例语句将有其他明智的工作。

这里喜欢：

i.ItemNumber like '%-NLA' or 
i.ItemNumber like '%-NLA DEL' or 
i.ItemNumber like '%-NLAmod'

请帮助我。

select count(p.SkuID) as Total from Product p (nolock)
join items i (nolock) on p.SkuID = i.SkuID
join #TempProductLine pl (nolock) on i.ProductLineID = pl.ProductLineID where p.ProductID = @ParentId and i.IsSelling = 1 and
case when @IsWebSite = 1 then
    PATINDEX ('%-NLA DEL',i.ItemNumber) 
end = 0

Answer 1

尝试以下：

select count(p.SkuID) as Total from Product p (nolock)
join items i (nolock) on p.SkuID = i.SkuID
join #TempProductLine pl (nolock) on i.ProductLineID = pl.ProductLineID   where 
p.ProductID = @ParentId and i.IsSelling = 1 and
i.ItemNumber like '%' + 
case 
when @IsWebSite = 1 then '-NLA'
when @IsWebSite = 2 then '-NLA DEL'
when @IsWebSite = 3 then '-NLAmod'
else '' end

或简单地喜欢：

select count(p.SkuID) as Total from Product p (nolock)
join items i (nolock) on p.SkuID = i.SkuID
join #TempProductLine pl (nolock) on i.ProductLineID = pl.ProductLineID   where 
p.ProductID = @ParentId and i.IsSelling = 1 and
(
@IsWebSite = 1 and i.ItemNumber like '%-NLA' or
@IsWebSite = 2 and i.ItemNumber like '%-NLA DEL' or
@IsWebSite = 3 and i.ItemNumber like '%-NLAmod'
)

Answer 2

也许可以使用类似的工作？

SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';

所以将其像i.ItemNumber SIMILAR TO '%(-NLA|-NLA DEL|-NLAmod)';

结合在一起

您可能想尝试的其他一些事情：

LIKE ANY(ARRAY['%-NLA','%-NLA DEL','%-NLAmod'])