17,089
社区成员
发帖
与我相关
我的任务
分享
select d.e,d.e+c.m from
(select s+d smax,s,d,e-s as m from t2)
c ,
(select b.e,a.s-b.e as m from
(select rownum rn,s,e from t1 order by s) a,
(select rownum rn,s,e from t1 order by s) b
where a.rn=(b.rn+1))
d
where c.s<=d.e and c.smax>=d.e and d.m>=c.m
结果
14 17
CREATE TABLE ssys.t1
(
s NUMBER(2),
e NUMBER(2)
)
PCTFREE 10
PCTUSED
INITRANS 1
MAXTRANS 255
TABLESPACE dbmistabspace
STORAGE (
INITIAL 1048576
NEXT 1048576
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELIST GROUPS 0
FREELISTS 0
)
/
CREATE TABLE ssys.t2
(
s NUMBER(2),
e NUMBER(2),
d NUMBER(2)
)
PCTFREE 10
PCTUSED
INITRANS 1
MAXTRANS 255
TABLESPACE dbmistabspace
STORAGE (
INITIAL 1048576
NEXT 1048576
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELIST GROUPS 0
FREELISTS 0
)
/
INSERT INTO SSYS.T1
(S,E)
VALUES
(6,8)
/
INSERT INTO SSYS.T1
(S,E)
VALUES
(10,14)
/
INSERT INTO SSYS.T1
(S,E)
VALUES
(18,22)
/
INSERT INTO SSYS.T2
(S,E,D)
VALUES
(7,10,7)
/
最后
select d.e,d.e+c.d from
(select s+d smax,s,d,e-s as m from t2)
c ,
(select b.e,a.s-b.e as m from
(select rownum rn,s,e from t1 order by s) a,
(select rownum rn,s,e from t1 order by s) b
where a.rn=(b.rn+1))
d
where c.s<=d.e and c.smax>=d.e and d.m>=c.m
结果
14 21