Sql 查询 - 摆脱硬编码值

Question

我有以下查询:

Select Name,
       case when charindex('I',a.S_Data) > 0 then 1 else 0 end as Illustrated,
       case when charindex('FP',a.S_Data) > 0 then 1 else 0 end as FrontPage,
       case when charindex('BP',a.S_Data) > 0 then 1 else 0 end as BackPage,
       case when charindex('ELP',a.S_Data) > 0 then 1 else 0 end as EDLP,
       case when charindex('PR',a.S_Data) > 0 then 1 else 0 end as SpecialPromo
From Table1

我想做的是将这些过滤器值存储在某种查找表或设置表中。

我正在努力解决如何从查找表中提取值以用于此查询。

Answer 1

我至少能想到两个选择……

CREATE TABLE constants (
  id               AS INT,
  Illustrated      AS VARCHAR(3),
  FrontPage        AS VARCHAR(3),
  BackPage         AS VARCHAR(3),
  EDLP             AS VARCHAR(3),
  SpecialPromo     AS VARCHAR(3)
)

INSERT INTO constants SELECT 1, 'I', 'FP', 'BP', 'ELP', 'PR'

SELECT
  Name,
  CASE WHEN CHARINDEX(constants.Illustrated, data.S_Data) > 0 THEN 1 ELSE 0 END   AS Illustrated,
  etc, etc
FROM
  data
INNER JOIN
  constants
    ON constants.id = 1

或者...

CREATE TABLE constants (
  constant_set_id  AS INT,
  constant_name    AS VARCHAR(16),
  value            AS AS VARCHAR(3)
)

INSERT INTO constants SELECT 1, 'Illustrated',  'I'
INSERT INTO constants SELECT 1, 'FrontPage',    'FP'
INSERT INTO constants SELECT 1, 'BackPage',     'BP'
INSERT INTO constants SELECT 1, 'EDLP',         'ELP'
INSERT INTO constants SELECT 1, 'SpecialPromo', 'PR'

SELECT
  Name,
  MAX(CASE WHEN constants.constant_name = 'Illustrated' AND CHARINDEX(constants.value, data.S_Data) > 0 THEN 1 ELSE 0 END)   AS Illustrated,
  etc, etc
FROM
  data
INNER JOIN
  constants
    ON constants.constant_set_id = 1
GROUP BY
  data.name

两者都让您拥有多组不同的常量。一种是无需更改架构即可扩展的，尽管查询仍需要更改。

这两种方法的主要优点是您可以在其他地方重用常量，但将它们存储在一个集中位置一次。仅当/当常量中的值需要更新时才相关。通过间接重用。