mysql - 使用 CASE 的 SQL 查询

Question

我正在写入查询以获取 email1Opened 和 email1Sent，即单行中 email_type 1 的 yes/no。不在多行上。那么你能帮我单行打印吗？

select user_id, 
 business_id, 
 email_status,
 id, email_type,
 case when email_status='open' and email_type = 1 then 'YES' else 'NO' end email1Opened,
       case when email_status='Sent' and email_type = 1 then 'YES' else 'NO' end email1Sent
from email_track where business_id = 10;

Answer 1

如果您只想获取 email_type 1，则应在查询中使用另一个 where:

select user_id, 
 business_id, 
 email_status,
 id, email_type,
 case when email_status='open' then 'YES' else 'NO' end as email1Opened,
 case when email_status='Sent' then 'YES' else 'NO' end as email1Sent
from email_track where business_id = 10 and email_type = 1;