DRY T语句

Question

是否有一种DRYer方法来编写这个case语句？

case 
  when try_convert(int, [MIXED USE FIELD]) >= 0 and try_convert(int, [MIXED USE FIELD]) <= 5 then 1
  ...
  when try_convert(int, [MIXED USE FIELD]) > 20 then 2
  else -9
end

这个语法不起作用：

case try_convert(int, [MIXED USE FIELD])
  when between 0 and 5 then 1
  when between 6 and 10 then 2
  when between 11 and 20 then 3
  when > 20 then 4
  else -9
end

编辑**

样本数据：

0 to 2
> 10
6

由于TRY_CONVERT只在离散值上工作，所以我不确定子查询是否有用。目前，我正在处理不同代码子分支中的范围值。

Answer 1

您可以执行一个获取try_convert结果的子查询，然后外部查询运行case语句。就像这样：

-- use equivalence
SELECT  CASE 
        WHEN s.converted >= 0 AND s.converted < 6 THEN 1
        WHEN s.converted >= 6 AND s.converted < 11 THEN 2
        WHEN s.converted >= 11 AND s.converted < THEN 3
        WHEN s.converted > 20 THEN 4
        END calc
FROM    (
            SELECT converted = try_convert([MIXED_USE_FIELD])
            FROM TABLE
        ) s

-- use BETWEEN
SELECT  CASE 
        WHEN s.converted BETWEEN 0 AND 5 THEN 1
        WHEN s.converted BETWEEN 6 AND 9 THEN 2
        WHEN s.converted BETWEEN 11 AND 20 THEN 3
        WHEN s.converted > 20 THEN 0 
        END calc
FROM    (
            SELECT converted = try_convert([MIXED_USE_FIELD])
            FROM TABLE
        ) s

Answer 2

你可以这样做：

...
    case
        when tc.c between 0 and 5 then 1
        when tc.c between 6 and 10 then 2
        when tc.c between 11 and 20 then 3
        when tc.c > 20 then 4
        else -9
    end
from ... as a
    outer apply (select ry_convert(int, [MIXED USE FIELD]) as tc) as c

Answer 3

在这种情况下，我不会试图重构。如果您的目标是可读的、可维护的代码，那么代替try_convert()的足够的描述性名称将与函数本身一样冗长。没有足够的额外清晰度来证明额外的间接。

SQL不是一种漂亮的语言。别把口红涂在猪身上。

select
  case 
    when try_convert(int, [MIXED USE FIELD]) between  0 and  5 then 1
    when try_convert(int, [MIXED USE FIELD]) between  6 and 10 then 2
    when try_convert(int, [MIXED USE FIELD]) between 11 and 20 then 3
    when try_convert(int, [MIXED USE FIELD]) > 20              then 4
    else -9
  end
from MyTable t1

相对于：

select
  case 
    when [MIXED USE FIELD as int] between  0 and  5 then 1
    when [MIXED USE FIELD as int] between  6 and 10 then 2
    when [MIXED USE FIELD as int] between 11 and 20 then 3
    when [MIXED USE FIELD as int] > 20              then 4
    else -9
  end
from MyTable t1
cross apply (
  select try_convert(int, [MIXED USE FIELD]) as [MIXED USE FIELD as int]
) t2

另一种方法是以SQL方式重写业务规则:将业务规则封装在数据中，而不是在代码中。将这些范围移动到表中。

CREATE TABLE MyRangeFilterTable (
  [RangeFrom] int DEFAULT -2147483648
 ,[RangeTo]   int DEFAULT  2147483647
 ,[Value]     int
)

INSERT MyRangeFilterTable VALUES
  ( 0,   5, 1)
 ,( 6,  10, 2)
 ,(11,  20, 3)
 ,(20, DEFAULT, 4)
 ,(NULL, NULL, -9)

SELECT
  [Value]
FROM MyTable
INNER JOIN MyRangeFilterTable
  ON (TRY_CONVERT(int, [MIXED USE FIELD]) BETWEEN [RangeFrom] AND [RangeTo])
  OR (TRY_CONVERT(int, [MIXED USE FIELD]) IS NULL AND [RangeFrom] IS NULL)

现在您可以在不更改代码的情况下修改规则。