您好,我正在尝试在 Band 属性中编写 If 语句,但没有成功 T_T。我想做的是根据所有者类别、棚屋和供水属性设置单个角色 A、B、C、D。任何帮助都会很棒^^。
CREATE TABLE Rent_Band
(
Plot_ID NUMBER(3),
Band VARCHAR(1)
IF SELECT * FROM Rent_Band WHERE Owner_Category = 'Council' AND WHERE Sheds ='No' THEN Band ='A'
ELSE IF SELECT * FROM Rent_Band WHERE Owner_Category = 'Private' AND WHERE Water_Supply ='Yes' THEN Band ='D'
ELSE IF SELECT * FROM Rent_Band WHERE Owner_Category = 'Council' AND WHERE Water_Supply ='No' AND WHERE Sheds ='Yes' THEN Band ='B'
ELSE IF SELECT * FROM Rent_Band WHERE Owner_Category = 'Private' AND WHERE Water_Supply ='No' AND WHERE Sheds ='No' THEN Band ='B'
ELSE THEN Band = 'C',
Rent_Charge NUMBER(4),
Owner_Category VARCHAR(10) CONSTRAINT Checking_Owner_Category CHECK((Owner_Category='Private') OR (Owner_Category='Council')),
Sheds VARCHAR(3) CHECK((Sheds='Yes') OR (Sheds='No')),
Water_Supply VARCHAR(3) CHECK((Water_Supply='Yes') OR (Water_Supply='No'))
)
Read 文档在虚拟列上:
CREATE TABLE Rent_Band
(
Plot_ID NUMBER(3),
Band VARCHAR(1) AS
(CASE WHEN Owner_Category = 'Council' AND Sheds ='No' THEN 'A'
WHEN Owner_Category = 'Private' AND Water_Supply ='Yes' THEN 'D'
WHEN Owner_Category = 'Council' AND Water_Supply ='No' AND Sheds ='Yes' THEN 'B'
WHEN Owner_Category = 'Private' AND Water_Supply ='No' AND Sheds ='No' THEN 'B'
ELSE 'C' END),
Rent_Charge NUMBER(4),
Owner_Category VARCHAR(10) CONSTRAINT Checking_Owner_Category CHECK((Owner_Category='Private') OR (Owner_Category='Council')),
Sheds VARCHAR(3) CHECK((Sheds='Yes') OR (Sheds='No')),
Water_Supply VARCHAR(3) CHECK((Water_Supply='Yes') OR (Water_Supply='No'))
)
顺便说一句,我强烈推荐:
- using a
bit
字段而不是Yes
/No
约束为Sheds
and Water_Supply
(节省空间和开销检查约束,并在大多数应用程序语言中直接转换为布尔值)
- 使用查找表
Owner_Category
出于基本相同的原因而不是字符串约束
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)