我有兩張桌子
員工
| 員工ID | 姓名 | 作業 |
|---|---|---|
| 123 | 大衛 | 是的 |
| 124 | 提摩太 | 是的 |
| 125 | 馬克西 | 不 |
| 126 | 阿比 |
員工宿舍
| ID | 員工ID | EmployeeSpclField | EmployeeSpclValue |
|---|---|---|---|
| 1 | 123 | 已婚 | 是的 |
| 2 | 123 | 國籍 | 墨西哥 |
| 3 | 123 | 作業 | 鉗工 |
| 4 | 124 | 已婚 | 是的 |
| 5 | 124 | 國籍 | ARG |
| 6 | 125 | 作業 | 司機 |
我需要一個基于 Bind 值 Employee id 回傳 1 或 0 的 Sql 查詢，我需要驗證以下條件
- 作業不應為空 2）國籍不應為空且他應屬于 MEX
那我該怎么做呢？我正在建造這樣的東西
select 1 from Employee emp ,EmployeeResidence er
where
employeeid=:empid and emp.job is not null and
我正在努力包含 EmployeeSpclField 列值來檢查它是否不為 null 并且屬于 MEX
uj5u.com熱心網友回復：
我可能會避免加入，而是在此處使用存在邏輯：
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');
uj5u.com熱心網友回復：
選擇一個 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;
uj5u.com熱心網友回復：
嘗試這個：
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')
uj5u.com熱心網友回復：
一種選擇可能是使用條件聚合來計算所需的條件
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()功能是用來考慮可能有重復記錄Nationality的值EmployeeSpclField每任何列EmployeeId列的值。
Demo
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/322829.html
上一篇：如何避免在資料庫中創建新列
下一篇：獲取x.66和x.99之間的分數