SQL Server在where语句中以select case形式返回列表

Question

我有以下疑问：

SELECT  cpa_0.[Period] as "Period", cpa_0.[Account] as "Account", cpa_0.[Center] as "Center", cpa_0.[Scenario] as "Scenario", cpa_0.[Effective_Date] as "Effective_Date" 
     FROM [dbo].[GLDetail] cpa_0 
     WHERE cpa_0.[Center] =  $$Centers-VALUE$$ and
           cpa_0.[Account] = $$Accounts-VALUE$$ and
           cpa_0.[Period] = case  
           when $$View-VALUE$$ = 'Periodic' then $$Periods-VALUE$$
           when $$View-VALUE$$ = 'Y_T_D' then ('2017-09','2017-08')
           end

值$$是变量传递，它们工作得很好。我的问题是在查询的最后一行，当$$View-VALUE$$ = 'Y_T_D‘then ('2017-09'，'2017-08')时，这不起作用。当我的变量等于Y_T_D时，我需要返回一个列表，我该怎么做呢？

问候

Answer 1

WHERE子句是一个布尔表达式。这里不需要使用CASE WHEN额外创建布尔表达式；只需使用AND和OR即可。

SELECT 
  Period,
  Account
  Center,
  Scenario,
  Effective_Date
FROM dbo.GLDetail  
WHERE Center =  $$Centers-VALUE$$ 
  AND Account = $$Accounts-VALUE$$ 
  AND 
  (
    ($$View-VALUE$$ = 'Periodic' AND Period = $$Periods-VALUE$$)
     OR
    ($$View-VALUE$$ = 'Y_T_D' AND Period IN ('2017-09', '2017-08'))
  );

Answer 2

您可以像这样更改where子句：

SELECT  cpa_0.[Period] as "Period", cpa_0.[Account] as "Account", cpa_0.[Center] as "Center", cpa_0.[Scenario] as "Scenario", cpa_0.[Effective_Date] as "Effective_Date" 
 FROM [dbo].[GLDetail] cpa_0 
 WHERE cpa_0.[Center] =  $$Centers-VALUE$$ and
       cpa_0.[Account] = $$Accounts-VALUE$$ and
       case  
       when $$View-VALUE$$ = 'Periodic' AND cpa_0.[Period] =  $$Periods-VALUE$$ THEN 1
       when $$View-VALUE$$ = 'Y_T_D' AND cpa_0.[Period] IN ('2017-09','2017-08') THEN 1
       ELSE 0
       END = 1