在CASE表达式使用TSQL大于号(Using tsql greater than sign in

(case [dbo].[YearsInService]([DateEngaged],getdate())
   when (0) then (0) 
   when (1) then (4) 
   when (2) then (8)
    when (3) then (12)
     when (4) then (32) 
     when (5) then (40) 
     when (6) then (48) 
     when (7) then (56) 
     when (8) then (104) 
     when (9) then (117) 
     when (10) then (150) else (-1) end)

现在，在我的最后一行，我怎么能说是10及以上的应为150退换吗？

Answer 1:

你不能， CASE YourFunction WHEN ...仅仅是为了平等。如果你需要使用“大于”，你需要这样重写你的表达：

CASE WHEN [dbo].[YearsInService]([DateEngaged],getdate()) = 0 THEN 0
WHEN [dbo].[YearsInService]([DateEngaged],getdate()) = 1 THEN 4
WHEN.....
WHEN [dbo].[YearsInService]([DateEngaged],getdate()) >= 10 THEN 150 ELSE -1 END

Answer 2:

您正在使用一个Simple Case statement ，其中的逻辑表达式是不允许的。您需要使用Searched CASE expression 。但是，在你的情况，因为你正在使用的功能将是位昂贵得到每个表达式的函数的返回值。

这里是MSDN链接，既简单情况和CASE搜索语法

我建议你使用一个sub query with a Searched case为波纹管。

select case when results = 0 then 0
            when results = 1 then 4
            ...
            when results >= 10 then 150
            else -1 end as CaseResults
from (select [dbo].[YearsInService]([DateEngaged],getdate()) results
      from yourTable
     ) Temp

Answer 3:

从http://msdn.microsoft.com/en-us/library/ms181765.aspx

SELECT 
  CASE 
     WHEN MIN(value) <= 0 THEN 0 
     WHEN MAX(1/value) >= 100 THEN 1 
  END 
FROM Data

您可以使用WHEN子句中的任何布尔表达式

case 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 0) then (0) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 1) then (4) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 2) then (8)
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 3) then (12)
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 4) then (32) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 5) then (40) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 6) then (48) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 7) then (56) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 8) then (104) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) = 9) then (117) 
  when ([dbo].[YearsInService]([DateEngaged],getdate()) >= 10) then (150) 
  else (-1) end

你应该在计算结果为寿前一个变量保存[DBO]。[YearsInService]（[DateEngaged]，GETDATE（））。

Answer 4:

假设（从您的评论），当DateEngaged是NULL ，它会导致YearsInService是NULL ，那么我会删除当前ELSE条款，然后利用它来进行all other cases ，是这样的：

case COALESCE([dbo].[YearsInService]([DateEngaged],getdate()),-1)
when (-1) then (-1)
when (0) then (0) 
when (1) then (4) 
when (2) then (8)
when (3) then (12)
 when (4) then (32) 
 when (5) then (40) 
 when (6) then (48) 
 when (7) then (56) 
 when (8) then (104) 
 when (9) then (117) 
 else (150) end

如果有一个关于未来的过时关注DateEngaged值，我会处理的里面YearsInService ，而不是尝试在处理它CASE表达式。

Answer 5:

我喜欢@kaf的回答，只是想补充一点，你可以用这种减少案件的数量

select case when results BETWEEN 0 AND 3 then results * 4
            when results BETWEEN 4 AND 7 then results * 8
            when results BETWEEN 8 AND 9 then results * 13
            when results >= 10 then 150
            else -1 
        end as CaseResults
from (select [dbo].[YearsInService]([DateEngaged],getdate()) results
      from yourTable
     ) Temp

Answer 6:

case [dbo].[YearsInService]([DateEngaged],getdate())
   when (0) then (0) 
   when (1) then (4) 
   when (2) then (8)
    when (3) then (12)
     when (4) then (32) 
     when (5) then (40) 
     when (6) then (48) 
     when (7) then (56) 
     when (8) then (104) 
     when (9) then (117) 
     when IIF(case [dbo].[YearsInService]([DateEngaged],getdate()) >= 10 
           ,[dbo].[YearsInService]([DateEngaged],getdate())
           ,10) 
        then (150) 
     else (-1) end

这相当于所有高于10的情况下的函数的值。

文章来源: Using tsql greater than sign in CASE Expression