时间戳

首页 > 解决方案 > 甲骨文 SQL | 如果一列包含一个值，那么它将排除同一列中的不同值

sql - 甲骨文 SQL | 如果一列包含一个值，那么它将排除同一列中的不同值

问题描述

我有这个查询返回它下面的数据

select LISTAGG(d.DOCUMENT_TYPE_CD, ',') WITHIN GROUP (ORDER BY D.DOCUMENT_TYPE_CD) as value
from test_table d;

VALUE
---------
CI,ECI,POA

现在我正在尝试在出现“ECI”值时添加一个条件，它应该在结果中排​​除“CI”，如下面的

VALUE
---------
ECI,POA

我尝试在 where 条件下使用 case 语句提示错误

select LISTAGG(d.DOCUMENT_TYPE_CD, ',')
WITHIN GROUP (ORDER BY D.DOCUMENT_TYPE_CD) as value
from test_table d
where CASE d.DOCUMENT_TYPE_CD
WHEN 'ECI' THEN d.DOCUMENT_TYPE_CD <> 'CI'
END;


ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
Error at Line: 7 Column: 36

有没有其他方法可以解决这个问题？

标签： sqloracle

解决方案

看看这是否有帮助；阅读代码中的注释。

SQL> with
  2  test (id, document_type_cd) as
  3    -- sample data
  4    (select 1, 'ECI' from dual union all
  5     select 1, 'CI'  from dual union all
  6     select 1, 'POA' from dual union all
  7     --
  8     select 2, 'CI'  from dual union all
  9     select 2, 'POA' from dual union all
 10     --
 11     select 3, 'XYZ' from dual union all
 12     select 3, 'ABC' from dual
 13    ),

 14  temp as
 15    -- see whether CI and ECI exist per each ID
 16    (select id,
 17       sum(case when document_type_cd = 'CI' then 1 else 0 end) sum_ci,
 18       sum(case when document_type_cd = 'ECI' then 1 else 0 end) sum_eci
 19     from test
 20     group by id
 21    ),
 22  excl as
 23    -- exclude CI rows if ECI exist for that ID
 24    (select a.id,
 25            a.document_type_cd
 26     from test a join temp b on a.id = b.id
 27     where a.document_type_cd <> case when b.sum_ci > 0 and b.sum_eci > 0 then 'CI'
 28                                      else '-1'
 29                                 end
 30    )
 31  -- finally:
 32  select e.id,
 33         listagg(e.document_type_cd, ',') within group (order by e.document_type_cd) result
 34  from excl e
 35  group by e.id;

        ID RESULT
---------- --------------------
         1 ECI,POA
         2 CI,POA
         3 ABC,XYZ

SQL>

推荐阅读