c语言sql消除硬编码,SQL查询 - 摆脱硬编码值的

我能想到的至少有两个选项...

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

都让你拥有多组不同的常量。一种是在不改变模式的情况下可扩展的，尽管查询仍然需要改变。

这两种方法的主要优点是，您可以重复使用其他常量，但将它们一次存储在中央位置。只有当/常量中的值需要更新时才适用。通过间接再利用。