Oracle Case

Question

我有一个这样的表:

韩国三星XIAMO

您还需要另一个-mapping-表，它显示哪个操作系统属于哪个产品。是的，您可以将硬编码到查询中，但这通常是一个坏主意。你不能每次新产品出现在市场上就修改查询，对吗?你当然可以，但你不应该。

SQL> with mapping (product, os) as
2    (select 'APPLE'  , 'IOS'     from dual union all
3     select 'SAMSUNG', 'ANDROID' from dual union all
4     select 'HUAWEI' , 'ANDROID' from dual union all
5     select 'XIAMO'  , 'ANDROID' from dual
6    ),
7  your_table (id, product) as
8    (select 1, 'APPLE'   from dual union all
9     select 2, 'SAMSUNG' from dual union all
10     select 2, 'APPLE'   from dual union all
11     select 3, 'HUAWEI'  from dual union all
12     select 4, 'XIAMO'   from dual union all
13     select 4, 'APPLE'   from dual
14    )
15  select y.id,
16    max(case when p.os = 'IOS' then 'YES' else 'NO' end) as ios,
17    max(case when p.os = 'ANDROID' then 'YES' else 'NO' end) as android
18  from your_table y join mapping p on p.product = y.product
19  group by y.id
20  order by y.id;
ID IOS AND
---------- --- ---
1 YES NO
2 YES YES
3 NO  YES
4 YES YES
SQL>

您可以聚合以确保id只显示一次:

select id,  
MAX(case when product = 'APPLE' THEN 'YES' END) as 'IOS',
MAX(case when product in ('SUMSUNG', 'HUAWEI', 'XIAMO') THEN 'YES' END) AS 'ANDROID'
from table
GROUP BY id

这确保你得到一行每个id -但你得到NULL而不是'NO'。要解决这个问题，使用COALESCE。

select id,  
COALESCE(MAX(case when product = 'APPLE' THEN 'YES' END),'NO') as 'IOS',
COALESCE(MAX(case when product in ('SUMSUNG', 'HUAWEI', 'XIAMO') THEN 'YES' END),'NO') AS 'ANDROID'
from table
GROUP BY id

您可以根据品牌APPLE构建逻辑，因为这是它自己的操作系统(IOS)的唯一成员(APPLE)，例如

SELECT id,
DECODE(SIGN(SUM(DECODE(product,'APPLE',1,0))),1,'YES','NO') AS ios,
DECODE(SIGN(SUM(DECODE(product,'APPLE',0,1))),1,'YES','NO') AS android 
FROM t
GROUP BY id
ORDER BY id

，其中内部条件和SUM()通过id列分组聚合，signum函数用二进制数检查APPLE的存在/不存在，然后替换为YES/NO