请教一个SQL的写法

〇〇

lz还需要把期望结果贴出来

kangpengjie

重新编辑了一下帖子，期望结果已经贴上了

bfc99

kangpengjie 发表于 2017-2-20 08:54
重新编辑了一下帖子，期望结果已经贴上了

1、8楼的SQL不满足吗？

kelis2004

纯粹为了题目写的，用的fulljoin效率不高，在这个结果上不难写匹配条件。楼主可以试试
select id,listagg(matchstr,',') within group (order by id) str
from (
  select id,item_id ,
  listagg(idmatch,',') within group (order by idmatch) ,
  listagg(matchvalue,',') within group (order by idmatch) matchstr
  from (
  select a.id,a.item_id,a1.id as idmatch,value matchvalue from a full join a1
  on a.item_id = a1.id
  union all
   select a.id,a.item_id,a2.id as idmatch,value matchvalue from a full join a2
  on a.item_id = a2.id
  union all
  select  a.id,a.item_id,a3.id as idmatch,value matchvalue from a full join a3
  on a.item_id = a3.id
  )
  group by id ,item_id
)
group by id

kangpengjie

bfc99 发表于 2017-2-20 09:20
1、8楼的SQL不满足吗？

8楼的SQL满足了。现在是前台传来一个如下这样的表达式
a contains a1 and contains(a2 or a3)  或 a contains a1 or contains(a2 and a3)，
我需要根据这个表达式写出对应的SQL语句。这个表达式可能会非常复杂，有很多层的与或关系

bfc99

kangpengjie 发表于 2017-2-20 15:57
8楼的SQL满足了。现在是前台传来一个如下这样的表达式
a contains a1 and contains(a2 or a3)  或 a con ...

union 相当于你的表达式中的OR，
intersect 相当于你的表达式中的AND

“a contains a1 and contains (a2 or a2)"可以写成如下：
select id
   from a
   where a.item_id in (select id from a1)
intersect   --and
(select id
    from a
    where a.item_id in (select id form a2)
union     --or
  select id
    from a
   where a.item_id in (select id from a3)
);

"a contains a1 or contains(a2 and a3)"可以写成：
select id
   from a
   where a.item_id in (select id from a1)
union              --or
(select id
    from a
    where a.item_id in (select id form a2)
intersect       --and
  select id
    from a
   where a.item_id in (select id from a3)
);

newkid

1.
WITH all_data as (
SELECT 'a1' tname,id,value from a1
union all SELECT 'a2' tname,id,value from a2
union all SELECT 'a3' tname,id,value from a3
)
select a.id
  from a join all_data on a.item_id=all_data.id
group by a.id
having count(case when tname='a1' then 1 end)>0
       and count(case when tname in ('a2','a3') then 1 end)>0
;


2.
WITH all_data as (
SELECT 'a1' tname,id,value from a1
union all SELECT 'a2' tname,id,value from a2
union all SELECT 'a3' tname,id,value from a3
)
select a.id
  from a join all_data on a.item_id=all_data.id
group by a.id
having count(case when tname='a2' then 1 end)>0
       and count(case when tname='a3' then 1 end)>0
;

newkid

解析你的输入条件，构建输出我上面的动态SQL。

newkid

这样更容易些：

create or replace view test_contains
as
WITH all_data as (
SELECT 'a1' tname,id,value from a1
union all SELECT 'a2' tname,id,value from a2
union all SELECT 'a3' tname,id,value from a3
)
select a.id
      ,max(case when tname='a1' then 'Y' end) contain_a1
      ,max(case when tname='a2' then 'Y' end) contain_a2
      ,max(case when tname='a3' then 'Y' end) contain_a3
  from a join all_data on a.item_id=all_data.id
group by a.id
;

1. select id from test_contains where contain_a1='Y' and (contain_a2='Y' or contain_a3='Y');
2. select id from test_contains where contain_a1='Y' or (contain_a2='Y' and contain_a3='Y');