【Oracle】查询表总某列是否全部满足条件

01

有这样一张表

select '小白' name, 'A' Post
  from dual
union all
select '小白' name, 'C' Post
  from dual
union all
select '小白' name, 'B' Post
  from dual
union all
select '小绿' name, 'B' Post
  from dua

我们希望得到同时满足POST为ABC条件的人

02

select name, listagg(post, ',') within group(order by post) post
from (select '小白' name, 'A' Post
        from dual
      union all
      select '小白' name, 'C' Post
        from dual
      union all
      select '小白' name, 'B' Post
        from dual
      union all
      select '小绿' name, 'B' Post
        from dual)
group by name

03

select *
  from (select name, listagg(post, ',') within group(order by post) post
          from (select '小白' name, 'A' Post
                  from dual
                union all
                select '小白' name, 'C' Post
                  from dual
                union all
                select '小白' name, 'B' Post
                  from dual
                union all
                select '小绿' name, 'B' Post
                  from dual)
         group by name)
 where post like '%A%B%C%'

这样就得到了我们想要的效果,如果要串联其他的表,一定要进行排序处理,POST中间可以夹着其他数据,但是顺序不能错,否则无法查询出想要的结果