如何在 SQL 查询视图中使用 if 条件

Question

在SSMS中使用视图。

我想在视图中使用 if 条件并根据条件向视图添加列。

ALTER VIEW [dbo].[KnowledgeArticlesJsonOut] AS
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT KA.[Id]
,KA.[new_brandmultiselectstring]
,KA.[statecode]`enter code here`
,KA.[statuscode]
,KA.[knowledgearticleid]
,CAT.title as categorytitle
,CAT.categorynumber
From [CRM].[knowledgearticle] as KA
LEFT OUTER JOIN [CRM].[knowledgearticlescategories] AS KAC on KAC.knowledgearticleid = KA.Id
LEFT OUTER JOIN [CRM].[category] as CAT on KAC.categoryid = CAT.categoryid
GO

我想添加这样的条件：

1. 如果卡。new_brandmultiselectstring] 包含"170001"，然后170001为 "MRBrand"
2. 如果卡。[new_brandmultiselectstring] 包含 '170000' 则 170000 作为 'USSBrand'

在视图中。请帮我解决这个问题。

Answer 1

您可以使用case表达式来实现此目的，如下所示：

ALTER VIEW [dbo].[KnowledgeArticlesJsonOut] AS
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT KA.[Id]
, KA.[new_brandmultiselectstring]
, KA.[statecode]
, KA.[statuscode]
, KA.[knowledgearticleid]
, CAT.title AS categorytitle
, CAT.categorynumber
, CASE WHEN KA.[new_brandmultiselectstring] LIKE '%170001%' THEN 170001 ELSE NULL END AS MRBrand
, CASE WHEN KA.[new_brandmultiselectstring] LIKE '%170000%' THEN 170000 ELSE NULL END AS USSBrand
FROM [CRM].[knowledgearticle] AS KA
LEFT OUTER JOIN [CRM].[knowledgearticlescategories] AS KAC ON KAC.knowledgearticleid = KA.Id
LEFT OUTER JOIN [CRM].[category] AS CAT ON KAC.categoryid = CAT.categoryid
GO