例如:
表中数据都为int
现在有一个表A B
avalue bstart bend
1 1 3
2 5 6
4
8,现在要求是高效sql语句,查询要求:a的avalue值不在B的范围之内,查询结果应该是 4,8;
现在子查询效率比较低,求高效语句。
--4,8
select a.avalue from a
where a.avalue not in
(select a.avalue from a , b
where a.avalue between b.bstart and b.bend
);
表中数据都为int
现在有一个表A B
avalue bstart bend
1 1 3
2 5 6
4
8,现在要求是高效sql语句,查询要求:a的avalue值不在B的范围之内,查询结果应该是 4,8;
现在子查询效率比较低,求高效语句。
--4,8
select a.avalue from a
where a.avalue not in
(select a.avalue from a , b
where a.avalue between b.bstart and b.bend
);
(select 1 from b where a.avalue between b.bstart and b.bend)
(select a.avalue from a , b
where a.avalue between b.bstart and b.bend ) c
where a.avalue=c.avalue(+)
and c.avalue is null左连接稍微快点。
select a.avalue from a
where not exists
(select 1
from a , b
where a.avalue between b.bstart and b.bend
);
minus
select a.avalue from a,b
where a.avalue between between b.bstart and b.bend没测试,错了别怪
select * from table_2 for update
select *
from table_1 a
where not exists
(select 1 from table_2 b
where a.t_a >= b.t_a and a.t_a <= b.t_b)2樓的寫法也很好