17,377
社区成员
发帖
与我相关
我的任务
分享
with t_data as (
select 1 as id, 1 as wid,1 xh from dual union all
select 2 as id, 1 as wid,2 xh from dual union all
select 3 as id, 1 as wid,3 xh from dual union all
select 4 as id, 1 as wid,4 xh from dual union all
select 5 as id, 2 as wid,1 xh from dual union all
select 6 as id, 2 as wid,2 xh from dual union all
select 7 as id, 2 as wid,3 xh from dual)
select t1.id, t1.wid, t1.xh from t_data t1, (select wid,xh from t_data where id in (3,6)) t2 where t1.wid=t2.wid and t1.xh<t2.xh;
with tmp as (select wid,xh from t where id in (3,6))
select * from t left join tmp on t.wid=tmp.wid and t.xh<tmp.xh
select id,wid,xh
from t a
where EXISTS (select wid from t where INSTR(ID,'3,6')>0) and xh < (select xh from t where INSTR(ID,'3,6')>0 and wid=a.wid);
[/quote]
这个结果no rows。
看了下,这是啥INSTR(ID,'3,6')
我知道你是想查找3和6,但是'3,6'是当做一个字符串处理的吧
另外select id,wid,xh
from t a
where xh < (select xh from t where id in(3,6) and wid=a.wid);
好像是一样的效果( WID in (select wid from t where id in(3,6))去掉)。而且减少了扫描表的次数。[/quote]
您好我是新手,想问个问题,如果我的ID是3,我要查找的目标是33,6.请问也能查到么?
如果能,你这不是错了?[/quote]
你是说instr(3,'33,6') 这个?在3里面查找33,6? 3里面明显找不到33,6嘛。不过可以在33,6中查找3可以找到select id,wid,xh
from t a
where EXISTS (select wid from t where INSTR(ID,'3,6')>0) and xh < (select xh from t where INSTR(ID,'3,6')>0 and wid=a.wid);
[/quote]
这个结果no rows。
看了下,这是啥INSTR(ID,'3,6')
我知道你是想查找3和6,但是'3,6'是当做一个字符串处理的吧
另外select id,wid,xh
from t a
where xh < (select xh from t where id in(3,6) and wid=a.wid);
好像是一样的效果( WID in (select wid from t where id in(3,6))去掉)。而且减少了扫描表的次数。[/quote]
您好我是新手,想问个问题,如果我的ID是3,我要查找的目标是33,6.请问也能查到么?
如果能,你这不是错了?select id,wid,xh
from t a
where EXISTS (select wid from t where INSTR(ID,'3,6')>0) and xh < (select xh from t where INSTR(ID,'3,6')>0 and wid=a.wid);
[/quote]
这个结果no rows。
看了下,这是啥INSTR(ID,'3,6')
我知道你是想查找3和6,但是'3,6'是当做一个字符串处理的吧
另外select id,wid,xh
from t a
where xh < (select xh from t where id in(3,6) and wid=a.wid);
好像是一样的效果( WID in (select wid from t where id in(3,6))去掉)。而且减少了扫描表的次数。select id,wid,xh
from t a
where EXISTS (select wid from t where INSTR(ID,'3,6')>0) and xh < (select xh from t where INSTR(ID,'3,6')>0 and wid=a.wid);