Oracle按条件查询语句,oracle下根据条件关联语句进行查询

在个别业务中,可能需要根据关联表与主表中的关联关系进行连接才能得到想要的结果, 这有点像条件编译. 符合条件的才去做关联,不符合条件的就不做关联(或说让关联条件失败)

环境: oracle

请看代码:

Sql代码

create table A1(id number, val number);

create table A2(id number, type varchar2(2), val number, anyvalue varchar2(30));

insert into a1 values(1, 50);

insert into a1 values(1, 500);

insert into a1 values(2, 100);

insert into a1 values(2, 150);

insert into a1 values(3, 200);

insert into a1 values(3, 250);

insert into a1 values(4, 150);

insert into a1 values(4, 500);

insert into a2 values(1, '>', 100, '>100');

insert into a2 values(1, '<=', 100, '<=100');

insert into a2 values(2, '

insert into a2 values(3, '<=', 200, '<=200');

insert into a2 values(4, '>=', 150, '>=150');

select * from a1;

select * from a2;

select * from a1, a2

where a1.id = a2.id

and (case a2.type

when '>' then

(case when a1.val > a2.val then 1 else 0 end)

when '>=' then

(case when a1.val >= a2.val then 1 else 0 end)

when '<=' then

(case when a1.val <= a2.val then 1 else 0 end)

when '

(case when a1.val < a2.val then 1 else 0 end)

else

0

end) = 1;

drop table a1 purge;

drop table a2 purge;

作者 czwlucky

http://www.dengb.com/oracle/484572.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/484572.htmlTechArticle在个别业务中,可能需要根据关联表与主表中的关联关系进行连接才能得到想要的结果, 这有点像条件编译. 符合条件的才去做关联,不符合条...