在Oracle SQL中使用复杂的IF语句 [英] Using Complex IF Statement in Oracle SQL

问题描述

我正在尝试在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 a Yes/No constraint for Sheds and Water_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屋！