Oracle :有条件地从LISTAGG中排除数据
Oracle :有条件地从LISTAGG中排除数据
提问于 2014-09-04 20:41:19
我有一个这样的表结构：
username reason suspended
1 X 0
1 (null) 1
1 Y 1
2 Z 0
2 P 1
2 (null) 0
2 Q 1 我想做一个原因列的listagg和挂起列的sum，但是我想从listagg和sum中排除特定的行，其中reason不是null并且suspended = 0。生成的数据应该类似于：
username reason suspended
1 X 0
1 (null);Y 1+1
2 Z 0
2 P;(null);Q 1+0+1 友善的帮助(请原谅我的格式不好)
回答 1
Stack Overflow用户
发布于 2014-09-04 21:31:53
试试这个：
with t as (
select 1 id, 'x' reason, 0 suspended from dual union all
select 1, null, 1 from dual union all
select 1, 'y', 1 from dual union all
select 2 id, 'x', 0 reason from dual union all
select 2, null, 1 from dual
)
select id
, listagg(case when reason is null then '(null)' else reason end,';') within group(order by reason) res
, listagg(cast(suspended as char), '+') within group(order by reason) res1
from t
where suspended != 0
group by id
union
select id
, reason
, suspended || '' suspended
from t
where suspended = 0
order by id
/看看它是如何工作的：
SQL> ed
Wrote file afiedt.buf
1 with t as (
2 select 1 id, 'x' reason, 0 suspended from dual union all
3 select 1, null, 1 from dual union all
4 select 1, 'y', 1 from dual union all
5 select 2 id, 'x', 0 reason from dual union all
6 select 2, null, 1 from dual
7 )
8 select id
9 , listagg(case when reason is null then '(null)' else reason end,';') within group(order by reason) res
10 , listagg(cast(suspended as char), '+') within group(order by reason) res1
11 from t
12 where suspended != 0
13 group by id
14 union
15 select id
16 , reason
17 , suspended || '' suspended
18 from t
19 where suspended = 0
20* order by id
SQL> /
ID RES RES1
---------- -------------------- --------------------
1 x 0
1 y;(null) 1+1
2 (null) 1
2 x 0页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25665821
复制相关文章
相似问题
腾讯云开发者
