CREATE TABLE tab_risk (
prod_id NUMBER(10),
prod_name VARCHAR2(30),
prod_filter VARCHAR2(30),
constraint pk_tab_risk primary key (prod_id)
);
insert into tab_risk values(1,'a','Falcon');
insert into tab_risk values(2,'b','Cars');
insert into tab_risk values(3,'c','Powerpoint');
insert into tab_risk values(4,'d','Zone');
CREATE TABLE ref_filter (
add_in_filter VARCHAR2(30)
);
insert into ref_filter values('Powerpoint');
insert into ref_filter values('Word');
insert into ref_filter values('Email');
我需要將 ref_filter 資料附加到特定的 prod_id。但我想知道如何做到這一點，或者這是否可能？就像目前 prod_id 1 一樣，prod_filter 是 'Falcon' 但是當我選擇
prod_id 1 它應該給我“Falcon”、“Powerpoint”、“Word”和“Email”。對于 prod_id 2 等也是如此。有沒有辦法實作這一目標？
uj5u.com熱心網友回復：
一種選擇是創建一個視圖并從中選擇（而不是從表中）：
SQL> create or replace view v_filter as
2 select r.prod_id, r.prod_filter
3 from tab_risk r
4 union
5 select r.prod_id, f.add_in_filter
6 from tab_risk r cross join ref_filter f;
View created.
SQL> select *
2 from v_filter
3 where prod_id = 1;
PROD_ID PROD_FILTER
---------- ------------------------------
1 Email
1 Falcon
1 Powerpoint
1 Word
SQL>
然后可以將視圖連接到tab_risk表以選擇其他列（如果需要）。
[編輯：這是您作為評論發布的查詢，已修復]
SQL> SELECT r.prod_id, r.prod_name, r.prod_filter
2 FROM tab_risk r
3 UNION
4 SELECT r.prod_id, r.prod_name, f.add_in_filter
5 FROM tab_risk r CROSS JOIN ref_filter f;
PROD_ID PROD_NAME PROD_FILTER
---------- ------------------------------ ------------------------------
1 a Email
1 a Falcon
1 a Powerpoint
1 a Word
2 b Cars
2 b Email
2 b Powerpoint
2 b Word
3 c Email
3 c Powerpoint
3 c Word
4 d Email
4 d Powerpoint
4 d Word
4 d Zone
15 rows selected.
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/357300.html
上一篇：查找具有指定差值的所有數字