条件运算符

Question

源

ename location sal 
A  X   10 
A  x   20 
B  y   30 
B  x   40 

ref_table

参考

ename location sal hike 
A  x   '<30' 10% 
B  y   '>30' 25% 

对于Ref_table的第一个记录我的逻辑应

Select Sal 
from source a 
left join 
Ref_table B 
where 
a.sal<=B.sal 

对于Ref_table的第二记录米Ÿ逻辑应该

Select Sal 
from source a 
left join 
Ref_table B 
where 
a.sal>=B.sal 

Answer 1

试试这个：

select Sal 
from source a 
left join Ref_table B on case when substring(B.Sal,1,1)='<' and a.sal<=cast(substring(B.sal,2,10) as int) then 1 when substring(B.Sal,1,1)='>' and a.sal>=cast(substring(B.sal,2,10) as int) then 1 else 0 end=1 

另外要小心，因为你用左连接并在其中将只返回满足您的条件（类似于内部连接）行。

Answer 2

试试这个：

select 
    s.sal, 
    s.ename 
from 
    source s inner join 
    ref_table r on 
    s.ename = r.ename 
where 
    (ename = 'a' and s.sal<=r.sal) or 
    (ename = 'b' and s.sal>=s.sal)