我有 3 張這樣的桌子
第一桌：人
| PID | 移動的 |
|---|---|
| 1 | 123 |
| 2 | 234 |
| 3 | 345 |
| 4 | 456 |
表二：報價
| PID | of_id | of_name |
|---|---|---|
| 1 | 11 | 報價 1 |
| 3 | 12 | 報價 2 |
| 3 | 13 | 報價 3 |
3td 表：服務
| PID | ser_id | ser_name |
|---|---|---|
| 2 | 10 | 序列號 2 |
| 1 | 11 | 序列 1 |
我想要這樣的表：
| PID | 移動的 | of_name | ser_name |
|---|---|---|---|
| 1 | 123 | 報價 1 | 序列 1 |
| 2 | 234 | 序列號 2 | |
| 3 | 345 | 報價 3 | |
| 3 | 34 | 報價 3 |
我試過了：
select distinct p.pid,p.mobile,o.of_name,s.ser_name
from person p
left join (select pid,of_name from offer) o on p.pid = o.pid
left join (select pid,ser_name from service) s on p.pid = s.pid
但我得到了：
| PID | 移動的 | of_name | ser_name |
|---|---|---|---|
| 1 | 123 | 報價 1 | 序列 1 |
| 2 | 234 | 序列號 2 | |
| 3 | 345 | 報價 3 | |
| 3 | 34 | 報價 3 | |
| 4 | 456 |
所以，我的桌子應該只包括那些有報價或服務的人。
uj5u.com熱心網友回復：
我的桌子應該只包括那些有報價或服務的人。
PID您可以通過檢查這些表中的 是否不是來過濾以僅包含存在優惠或服務的行NULL：
SELECT p.pid,
p.mobile,
o.of_name,
s.ser_name
FROM person p
LEFT OUTER JOIN offer o
ON p.pid = o.pid
LEFT OUTER JOIN service s
ON p.pid = s.pid
WHERE o.pid IS NOT NULL
OR s.pid IS NOT NULL
其中，對于樣本資料：
CREATE TABLE person (PID, MOBILE) AS
SELECT 1, 123 FROM DUAL UNION ALL
SELECT 2, 234 FROM DUAL UNION ALL
SELECT 3, 345 FROM DUAL UNION ALL
SELECT 4, 456 FROM DUAL;
CREATE TABLE offer (PID, of_id, of_name) AS
SELECT 1, 11, 'offer 1' FROM DUAL UNION ALL
SELECT 3, 12, 'offer 2' FROM DUAL UNION ALL
SELECT 3, 13, 'offer 3' FROM DUAL;
CREATE TABLE service (PID, ser_id, ser_name) AS
SELECT 2, 10, 'ser 2' FROM DUAL UNION ALL
SELECT 1, 11, 'ser 1' FROM DUAL;
輸出：
PID 移動的 OF_NAME SER_NAME 1 123 報價 1 序列 1 3 345 報價 2 空值 3 345 報價 3 空值 2 234 空值 序列號 2
db<>在這里擺弄
uj5u.com熱心網友回復：
嘗試如下
with cte as(select p.pid,p.mbl,
o.name as offername,s.name as service_name from person p
left join offer o on p.pid=o.pid
left join service s on p.pid=s.pid
) select pid,mbl,offername,service_name
from cte where pid in (select pid from offer
union
select pid from service)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432925.html