Table where子句中的列值

Question

我有两个表

员工

对对

我可能会避免连接，而在这里使用exists逻辑:

SELECT e.*
FROM Employee e
WHERE Job IS NOT NULL AND
EXISTS (SELECT 1 FROM EmployeeResidence er
WHERE er.EmployeeId = e.EmployeeId AND
er.EmployeeSpclField = 'Nationality' AND
er.EmployeeSpclValue = 'MEX');

选择0/1标志

select case when exists (
select 1 
from Employee emp 
join EmployeeResidence er 
on emp.employeeid = er.employeeid and er.EmployeeSpclField = 'Nationality' and er.EmployeeSpclValue = 'MEX'
where emp.employeeid=:empid and emp.job is not null ) then 1
else 0 end flag 
from dual;

试试这个:

Select
e.EmployeeID,
case when er.EmployeeID is null then 0 else 1 end as return_val
from
Employee e
left outer join
(select EmployeeId,
max(case when EmployeeSpclField='Married' then EmployeeSpclValue else NULL end) as married,
max(case when EmployeeSpclField='Nationality' then EmployeeSpclValue else NULL end) as Nationality,
max(case when EmployeeSpclField='Job' then EmployeeSpclValue else NULL end) as Job
from 
EmployeeResidence
group by
EmployeeId) er
on e.EmployeeId=er.EmployeeId
and e.Job is not null 
and (er.Nationality is not null and er.Nationality ='MEX')

一种选择可能是使用条件聚合来计算所需的条件

SELECT SIGN(NVL(SUM(CASE WHEN er.EmployeeSpclField = 'Nationality' 
AND er.EmployeeSpclValue = 'MEX' 
AND e.Job IS NOT NULL THEN 1 ELSE 0 END),0)) 
AS Result
FROM Employee e
JOIN EmployeeResidence er
ON er.EmployeeId = e.EmployeeId
WHERE e.EmployeeId = :empid

使用SIGN()函数，考虑到EmployeeSpclField列的Nationality值与EmployeeId列的任何值有重复记录的可能性。