Oracle :选择Oracle中的If Oracle语句

Question

我有两个表TestCustomer和TestEmail.I，我想检索基于email_stat的TestCustomer和外部布尔参数" noFlag ".If noFlag是"true“，获取与customer_id相关的所有电子邮件地址，其他只有"A”ones.Any帮助将不胜感激。

create table TestEmail(emai_id varchar(18),emailaddress varchar(20))

create table TestCustomer(customer_id varchar(18),emai_id varchar(18),email_stat char(1))

Insert Into TestEmail(emai_id,emailaddress)values('12345','abc@gmail.com');
Insert Into TestEmail(emai_id,emailaddress)values('123456','abcd@gmail.com');
Insert Into TestEmail(emai_id,emailaddress)values('123457','abcde@gmail.com');

Insert Into TestCustomer(customer_id,emai_id,email_stat)values('223459','12345','A');
Insert Into TestCustomer(customer_id,emai_id,email_stat)values('223458','123456','I');
Insert Into TestCustomer(customer_id,emai_id,email_stat)values('223459','123457','A');

预期输入：

customer_id=223458和noFlag=“true”

预期输出

​

预期输入：

customer_id=223458和noFlag='false‘

预期输出

空的结果。

Answer 1

SELECT [DISTINCT] emailaddress 
FROM TestEmail
JOIN TestCustomer USING (emai_id)
-- insert parameter-1 instead of 223458
WHERE TestCustomer.customer_id = 223458                      
-- insert parameter-2 instead of 'noFlag'
  AND TestCustomer.email_stat = CASE WHEN noFlag = 'true' 
                                     THEN TestCustomer.email_stat
                                     ELSE 'A'
                                     END

小提琴

Answer 2

如果你只想要电子邮件，你可以使用exists

select e.emailaddress
from testemail e
where exists (select 1
              from testcustomer c
              where c.email_id = e.email_id and
                    c.customer_id = :customer_id and
                    (c.email_stat = 'A' or :noflag = 'true')
             );