请问这个sql应该怎么写?

2018-10-29 05:02:00 375 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

妙啊，受益终生的20个写SQL的小细节，这次终于学到了前言 SQL 语句的优化对于研发人员也是非常重要的，高效的 SQL语句经常会给使一个业务逻辑的接口响应速度变得非常快。每一个好习惯都是一笔财富，本文分SQL后悔药，SQL性能优化，SQL规范优雅三个方向，写SQL语句一些小细节的分享！另外本人整理了20年面试题大全，包含spring、并发、数据库、Redis、分布式、dubbo、JVM、微服务等方面总结，下图是部分截图，需要的话点这里点这里，暗号CSDN。 1. 写完SQL先explain查看执行计划（SQL性能优化）日常开发写SQL的时候，尽量阅读详情

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

...全文

Unity3D实战：5分钟搞定RTSP监控视频播放（附完整代码）本文提供了一套在Unity3D中快速集成RTSP监控视频播放的实战方案。通过采用LibVLCSharp for Unity这一成熟稳定的跨平台中间件，开发者可以轻松实现RTSP流的解码与渲染，并附有完整的C#代码示例，帮助用户在5分钟内完成集成，适用于智慧园区、安防监控等3D可视化场景。阅读详情

leon51

基础和管理 17,377 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

whb00120012 2019-10-09

打赏
举报

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丙%'

weixin_44200964 2018-12-21

打赏
举报

[思路一：
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 '乙丙']

wildwolv 2018-11-20

打赏
举报

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

wildwolv 2018-11-20

打赏
举报

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

leon51 2018-11-17

打赏
举报

引用 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
;

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

whb00120012 2018-11-13

打赏
举报

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 ;

nayi_224 2018-11-13

打赏
举报

引用 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] 就是很正常的表链接条件，没什么特别的意思

leon51 2018-11-12

打赏
举报

引用 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行没有看懂，可否解释下，谢谢！

万匹羊驼 2018-11-05

打赏
举报

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 '%乙丙%';

chuwaqin 2018-11-03

打赏
举报

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

nayi_224 2018-10-29

打赏
举报

假设你这里没有重复的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
;

请问这个sql应该怎么写?

相关推荐