桌子OpenPO
| Shopping_Cart_No | Goods_Recipient_Emp_ID | accassgnmtownerid |
|---|---|---|
| 1001958413 | 160213 | 65658 |
| 1001661570 | 61875 | 61855 |
桌子Employee
| 員工編號 | 電子郵件 |
|---|---|
| 160213 | [email protected] |
| 61875 | [email protected] |
| 65658 | [email protected] |
| 61855 | [email protected] |
我有這兩個表，都由employee_number列鏈接。
我想顯示如下所示的資料。
預期結果
| 貨物_收件人_電子郵件 | Goods_Recipient_Emp_ID | accassgnmtownerid | accassgnmtownerid_Email |
|---|---|---|---|
| [email protected] | 160213 | 65658 | [email protected] |
| [email protected] | 61875 | 61855 | [email protected] |
嘗試使用左連接并能夠比較并僅選擇一個電子郵件列 Goods_Recipient_Emp_ID 或 op.accassgnmtownerid
SELECT
op.Goods_Recipient_Emp_ID, op.accassgnmtownerid,
te.Email AS accassgnmtownerid_Email
FROM
OpenPO op
LEFT JOIN
Employee te ON te.Employee_Number = op.Goods_Recipient_Emp_ID
嘗試使用子查詢 for accassgnmtownerid_Email，但沒有成功。
我們可以應用子查詢accassgnmtownerid_Email還是有其他解決方案？
uj5u.com熱心網友回復：
您需要employee使用兩個不同的別名連接表兩次。
例如：
select
r.email as Goods_Recipient_Email,
o.Goods_Recipient_Emp_ID,
o.accassgnmtownerid,
a.email as accassgnmtownerid_Email
from openpo o
join employee r on r.employee_number = o.Goods_Recipient_Emp_ID
join employee a on a.employee_number = o.accassgnmtownerid
uj5u.com熱心網友回復：
除了加入每個電子郵件的表之外，您還可以為每個電子郵件使用相關的子查詢，例如
select
(select Email from Employee e where e.employee_number = po.Goods_Recipient_Emp_ID) Goods_Recipient_Email,
po.Goods_Recipient_Emp_ID,
po.accassgnmtownerid,
(select Email from Employee e where e.employee_number = po.accassgnmtownerid) accassgnmtownerid_Email
from OpenPO po;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/489453.html
下一篇：透視資料以顯示特定列的唯一值