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

两者都让你拥有多组不同的常量.一个是可扩展的,无需更改架构,但查询仍需要更改.

这两种方法的主要优点是您可以重复使用其他常量,但是将它们存储在一个集中位置.只有当/常量中的值需要更新时,才有意义.通过间接重用.