在个别业务中,可能需要根据关联表与主表中的关联关系进行连接才能得到想要的结果, 这有点像条件编译. 符合条件的才去做关联,不符合条件的就不做关联(或说让关联条件失败)
环境: 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, '<', 150, '<150');
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 '<' then
(case when a1.val < a2.val then 1 else 0 end)
else
0
end) = 1;
drop table a1 purge;
drop table a2 purge;
作者 czwlucky