在Oracle SQL中使用复杂的IF语句 [英] Using Complex IF Statement in Oracle SQL
本文介绍了在Oracle SQL中使用复杂的IF语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试在Band Attribute中编写If语句,但没有成功T_T.我想做的是根据owner_category,Sheds和Water_Supply属性使用单个字符A,B,C,D.任何帮助都会很棒^^.
Hi i'm trying to write If statement in Band Attribute but without Success T_T. what Im trying to do is have single Character A, B, C, D depending on the owner_category, Sheds and Water_Supply attribute. Any Help will be Great ^^.
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'))
)
推荐答案
阅读有关虚拟列的文档:
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'))
)
顺便说一句,我强烈建议:
BTW, I would STRONGLY recommend:
- 为
Sheds
和Water_Supply
使用bit
字段而不是Yes
/No
约束(节省空间和开销检查约束,并在大多数应用程序语言中直接转换为布尔值) - 出于基本相同的原因,将查找表用于
Owner_Category
而不是字符串约束
- using a
bit
field instead of aYes
/No
constraint forSheds
andWater_Supply
(saves room and overhead checking constraints, and translates directly to boolean in most app languages) - using a lookup table for
Owner_Category
instead of a string constraint for basically the same reasons
这篇关于在Oracle SQL中使用复杂的IF语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文