请问这个sql应该怎么写?

[leon51]

要求从下表中找出step列同时存在"Step乙" 和"Step丙"、并且"Step乙" 在"Step丙"前面的PartNumber，下表的结果应该返回PartA
请问SQL 应该怎么写？谢谢！

[nayi_224]

假设你这里没有重复的step

with tab1 as (
select 'a' num, 'jia' typ, 1 ord from dual union all
select 'a' num, 'yi' typ, 2 ord from dual union all
select 'a' num, 'bing' typ, 3 ord from dual union all
select 'a' num, 'ding' typ, 4 ord from dual union all
select 'b' num, 'jia' typ, 1 ord from dual union all
select 'b' num, 'bing' typ, 2 ord from dual union all
select 'b' num, 'yi' typ, 3 ord from dual union all
select 'c' num, 'jia' typ, 1 ord from dual union all
select 'c' num, 'bing' typ, 2 ord from dual 
)
select t1.num
  from tab1 t1, tab1 t2
 where 1 = 1
   and t1.num = t2.num
   and t1.typ = decode(t2.typ, 'yi', 'bing', '')
   and t1.ord > t2.ord
;

[chuwaqin]

请把他考虑成2个表 用2个表结合做 结合key是 partnumber 表A抽的是乙 表B抽的是丙 并且表A的order 小于表B的order

[万匹羊驼]

with tab1 as (
select 'parta' num, '甲' typ, 1 ord from dual union all
select 'parta' num, '乙' typ, 2 ord from dual union all
select 'parta' num, '丙' typ, 3 ord from dual union all
select 'parta' num, '丁' typ, 4 ord from dual union all
select 'partb' num, '甲' typ, 1 ord from dual union all
select 'partb' num, '丙' typ, 2 ord from dual union all
select 'partb' num, '乙' typ, 3 ord from dual union all
select 'partc' num, '甲' typ, 1 ord from dual union all
select 'partc' num, '丙' typ, 2 ord from dual
)
select *
from（
select num,listagg(typ) within group(order by ord)tt
from tab1
group by num）t
where t.tt like '%乙丙%';

[leon51]

引用 1 楼 nayi_224 的回复:

假设你这里没有重复的step

with tab1 as (
select 'a' num, 'jia' typ, 1 ord from dual union all
select 'a' num, 'yi' typ, 2 ord from dual union all
select 'a' num, 'bing' typ, 3 ord from dual union all
select 'a' num, 'ding' typ, 4 ord from dual union all
select 'b' num, 'jia' typ, 1 ord from dual union all
select 'b' num, 'bing' typ, 2 ord from dual union all
select 'b' num, 'yi' typ, 3 ord from dual union all
select 'c' num, 'jia' typ, 1 ord from dual union all
select 'c' num, 'bing' typ, 2 ord from dual 
)
select t1.num
  from tab1 t1, tab1 t2
 where 1 = 1
   and t1.num = t2.num
   and t1.typ = decode(t2.typ, 'yi', 'bing', '')
   and t1.ord > t2.ord
;

非常感谢你的回复，不过17、18行没有看懂，可否解释下，谢谢！

[nayi_224]

引用 4 楼 leon51 的回复:

[quote=引用 1 楼 nayi_224 的回复:] 假设你这里没有重复的step

with tab1 as (
select 'a' num, 'jia' typ, 1 ord from dual union all
select 'a' num, 'yi' typ, 2 ord from dual union all
select 'a' num, 'bing' typ, 3 ord from dual union all
select 'a' num, 'ding' typ, 4 ord from dual union all
select 'b' num, 'jia' typ, 1 ord from dual union all
select 'b' num, 'bing' typ, 2 ord from dual union all
select 'b' num, 'yi' typ, 3 ord from dual union all
select 'c' num, 'jia' typ, 1 ord from dual union all
select 'c' num, 'bing' typ, 2 ord from dual 
)
select t1.num
  from tab1 t1, tab1 t2
 where 1 = 1
   and t1.num = t2.num
   and t1.typ = decode(t2.typ, 'yi', 'bing', '')
   and t1.ord > t2.ord
;

非常感谢你的回复，不过17、18行没有看懂，可否解释下，谢谢！[/quote] 就是很正常的表链接条件，没什么特别的意思

[whb00120012]

with tab2 as (
select 1 ording ， 'STEP乙' SETP FROM DUAL union all
select 2 ording , 'STEP丙' STEP FROM DUAL
)


select t1.PARTNUMBER
from (select distinct partnumber from tab1 ) t1
where EXISTS SELECT * FROM tab1 t2 , TAB2 t3 where t1.partnumber=t2.partnumber and t2.pt2.setp=t3.step and t2.order <t3.ording having count(*)=2 ;

[leon51]

引用 1 楼 nayi_224 的回复:

假设你这里没有重复的step

with tab1 as (
select 'a' num, 'jia' typ, 1 ord from dual union all
select 'a' num, 'yi' typ, 2 ord from dual union all
select 'a' num, 'bing' typ, 3 ord from dual union all
select 'a' num, 'ding' typ, 4 ord from dual union all
select 'b' num, 'jia' typ, 1 ord from dual union all
select 'b' num, 'bing' typ, 2 ord from dual union all
select 'b' num, 'yi' typ, 3 ord from dual union all
select 'c' num, 'jia' typ, 1 ord from dual union all
select 'c' num, 'bing' typ, 2 ord from dual 
)
select t1.num
  from tab1 t1, tab1 t2
 where 1 = 1
   and t1.num = t2.num
   and t1.typ = decode(t2.typ, 'yi', 'bing', '')
   and t1.ord > t2.ord
;

应该是可以，就是速度太慢了，

[wildwolv]

select distinct c.partnumber from
(select partnumber from table where step='Step乙') c,(select partnumber from table where step='Step丙') b where c.partnumber=b.partnumber
and c.ording > b.ording

[wildwolv]

select distinct c.partnumber from (select distinct partnumber,ording from table where step='Step乙') c,(select distinct partnumber,ording from table where step='Step丙') b where c.partnumber=b.partnumber
and c.ording > b.ording

[weixin_44200964]

[思路一：
with tmp as (
 select 'PartA' PartNum, '甲' step, 1 ording from dual union all
 select 'PartA' PartNum, '乙' step, 2 ording from dual union all
 select 'PartA' PartNum, '丙' step, 3 ording from dual union all
 select 'PartA' PartNum, '丁' step, 4 ording from dual union all
 select 'PartB' PartNum, '甲' step, 1 ording from dual union all
 select 'PartB' PartNum, '丙' step, 2 ording from dual union all
 select 'PartB' PartNum, '乙' step, 3 ording from dual 
 )
 select  *
   from(select PartNum,
               listagg(step) within group(order by ording) tt
          from tmp
         group by PartNum)t
   where t.tt like '%乙丙%';
 思路二：
 with tmp as (
 select 'PartA' PartNum, '甲' step, 1 ording from dual union all
 select 'PartA' PartNum, '乙' step, 2 ording from dual union all
 select 'PartA' PartNum, '丙' step, 3 ording from dual union all
 select 'PartA' PartNum, '丁' step, 4 ording from dual union all
 select 'PartB' PartNum, '甲' step, 1 ording from dual union all
 select 'PartB' PartNum, '丙' step, 2 ording from dual union all
 select 'PartB' PartNum, '乙' step, 3 ording from dual 
 )
 select PartNum
   from (select PartNum,
                listagg(step) within group (order by ording) l
           from tmp
          where step in ('乙','丙')
          group by PartNum)
  where l in '乙丙']

[whb00120012]

create or replace view v8 as
SELECT partnumber,listagg(step) within group(order by rowid) tt
FROM tab1 group by partnumber;
SELECT * FROM V8 WHERE TT like '%STEP乙STEP丙%'