sql - 避免 SQL 中出现太多 case 语句？

Question

我需要根据 apptypecode 和状态驱动 13 或 14 个 Datamatrix 条形码。我能够做到这一点，但我有太多案例陈述。

我想要完成的是，当应用程序页面 = 13 时，我会填充 13 个 DataMatrix，当应用程序页面 = 14 时，我会填充 14 个 DataMatrix，如下所示:- 第九页之后 Datamatrix MS3000000002421831E9 我开始使用字母 A = 10、B = 11、C = 12 D= 13 AND E = 14所以条形码看起来像这样:

我的问题:他们是否有另一种方法可以在不编写所有这些案例陈述的情况下完成此任务？

这是我的代码:

SELECT     C.CustomerID
      ,AT.ServiceTypeID
      ,[loadfilename]
      ,f.loadrowstatus
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EI') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EI') ELSE
      CASE WHEN F.primaryState IN('ND','AK') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D1')
      ELSE '' end END END AS BarCodeID
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E2') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E2') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D2')
      ELSE '' end END END AS DataMatrixCode2
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E3') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E3') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D3')
      ELSE '' end END END AS DataMatrixCode3
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E4') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E4') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D4')
      ELSE '' end END END AS DataMatrixCode4
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E5') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E5') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D5')
      ELSE '' end END END AS DataMatrixCode5
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E6') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E6') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D6')
      ELSE '' end END END AS DataMatrixCode6
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E7') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E7') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D7')
      ELSE '' end END END AS DataMatrixCode7
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E8') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E8') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D8')
      ELSE '' end END END AS DataMatrixCode8
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E9') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E9') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D9')
      ELSE '' end END END AS DataMatrixCode9
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EA') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EA') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DA')
      ELSE '' end END END AS DataMatrixCode10
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EB') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EB') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DB')
      ELSE '' end END END AS DataMatrixCode11
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EC') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EC') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DC')
      ELSE '' end END END AS DataMatrixCode12
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'ED') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'ED') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DD')
      ELSE '' end END END AS DataMatrixCode13
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EE') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EE') 
      --CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DD')
      ELSE '' end END AS DataMatrixCode14
     ,ISNULL(f.[AppTypeCode],'') AS [AppTypeCode]
      ,ISNULL([LanguageCode],'') AS [LanguageCode]
      ,ISNULL(f.[FirstName],'') AS [FirstName]
      ,ISNULL(f.[LastName],'') AS [LastName]
      ,ISNULL([primaryAddress1],'') AS [ServiceAddress1]
      ,ISNULL([primaryAddress2],'') AS [ServiceAddress2]
      ,ISNULL([primaryApartmentNumber],'') AS [ServiceApartmentNumber]
      ,ISNULL([primaryCity],'') AS [ServiceCity]
      ,ISNULL([primaryState],'') AS [ServiceState]
      ,ISNULL([primaryZipCode5],'') AS [ServiceZipCode5]
      ,ISNULL([primaryZipCode4],'') AS [ServiceZipCode4]
      ,ISNULL(f.[mailingAddress1],'') AS [BillingAddress1]
      ,ISNULL(f.[mailingAddress2],'') AS [BillingAddress2]
      ,ISNULL([mailingApartmentNumber],'') AS [BillingApartmentNumber]
      ,ISNULL([mailingCity],'') AS [BillingCity]
      ,ISNULL([mailingState],'') AS [BillingState]
      ,ISNULL([mailingZipCode5],'') AS [BillingZip5]
      ,ISNULL([mailingZipCode4],'') AS [BillingZip4]
      ,ISNULL([ReasonCodes],'') AS [ReasonCodes]
      ,ISNULL(f.[ContactPhoneNumber],'') AS [PhoneNumber]
      ,ISNULL([ContactEmailAddress],'') AS [EmailAddress]
      ,CASE WHEN at.servicetypeid='M' THEN ISNULL([CustomerAccountNumber],'') ELSE
      ISNULL(InternetAcctNum,'') END AS [CustomerAccountNumber]
     ,ISNULL([CustomerServiceNumber],'') AS [CustomerServiceNumber]
  FROM [outboundprocess].[DailyFulfillmentFile] F
  JOIN dbo.AppTypes AT ON AT.AppTypeID=f.AppTypeCode
  JOIN dbo.Customers c ON c.CustomerID=f.CustomerID
WHERE f.loadrowstatus = 'Loaded' AND AT.servicetypeid='M' AND c.ServiceAddressState<>'NV';

Answer 1

您应该创建一个映射表，其中包括 PrimaryState 和 AppType 组合以及它们映射到的 BarcodeID 值。然后您可以通过映射表 JOIN 您的表。

这样做还可以提高查询性能，因为 SQL Server 可以执行集合操作，而不是 CASE 语句所需的循环。

CREATE TABLE map
(
    PrimaryState VARCHAR(255),
    AppTypeCode INT,
    BarCodeID VARCHAR(255)
);

(适当调整数据类型)