對于特定要求，我必須準備 Oracle SQL，以下是表及其關系和所需的資料方案：
我可以通過將單個表與背景關系表連接，然后使用 Union All 來獲取所需的資料，但由于某些限制，我只能在此處使用 where 子句。請建議我是否可以通過 where 子句獲得相同的資料。
uj5u.com熱心網友回復：
一種在沒有 UNION 的情況下獲得 UNION ALL 的奇特方式
with t1(id,a) as
(
SELECT 1,'A' FROM DUAL UNION ALL
SELECT 2,'B' FROM DUAL UNION ALL
SELECT 3,'C' FROM DUAL
),
t2(id,b) as
(
SELECT 1,'X' FROM DUAL UNION ALL
SELECT 2,'Y' FROM DUAL UNION ALL
SELECT 3,'Z' FROM DUAL
)
select coalesce(t1.id, t2.id) id, a, b
from t1
full join t2 on 1=2
order by coalesce(t1.id, t2.id), a, b
uj5u.com熱心網友回復：
如果允許系統視圖
select
cxt.context_id, cxt.context_name
, job.Job_cxt_id, job.Job_name
, loc.loc_cxt_id, loc.geo_id
from Context_table cxt
left join ALL_TABLES tbl
on tbl.table_name in ('JOB_CONTEXT_TABLE', 'LOCATION_CONTEXT_TABLE')
left join Job_context_table job
on job.context_id = cxt.context_id
and tbl.table_name like 'JOB%'
left join location_context_table loc
on loc.context_id = cxt.context_id
and tbl.table_name like 'LOC%'
order by tbl.table_name
| CONTEXT_ID | CONTEXT_NAME | 作業_CXT_ID | 作業名稱 | LOC_CXT_ID | 地理標識 |
|---|---|---|---|---|---|
| 1001 | 營 | 10001 | 行政 | 空值 | 空值 |
| 1001 | 營 | 空值 | 空值 | 100001 | 12345 |
如果沒有，那么
select
nvl(cxt1.context_id, cxt2.context_id) as context_id
, nvl(cxt1.context_name, cxt2.context_name) as context_name
, job.Job_cxt_id, job.Job_name
, loc.loc_cxt_id, loc.geo_id
from Context_table cxt1
full join Context_table cxt2 on -0!=0
left join Job_context_table job
on job.context_id = cxt1.context_id
left join location_context_table loc
on loc.context_id = cxt2.context_id
order by cxt1.context_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/382368.html