表A如圖，
B表如圖。現在需要把AB欄位按B表的對應轉換中文，印象中CONNECT BY 可以，寫幾次都失敗了，請幫忙看看該怎么寫SQL？謝謝
uj5u.com熱心網友回復：
兩張表的資料，給些示例，要 insert 形式的。uj5u.com熱心網友回復：
這個問題比較簡單，請給出建表陳述句和測驗資料。uj5u.com熱心網友回復：
感謝兩位回復。測驗資料如下。/*
drop table T1;
drop table T2;
*/
CREATE TABLE T1 (
LX VARCHAR2(10),
DM VARCHAR2(20),
QX VARCHAR2(3000)
);
insert into T1 values('CK07','c512','about, accept, cancel, controlg, controlp, controlr, exit, help, locale, qbe_save, qbe_select');
insert into T1 values('CK07','mp002','about, accept, cancel, close, controlg, controlp, controlr, exit, help, locale, qbe_save, qbe_select');
insert into T1 values('CK08','mp306','about, accept, cancel, controlg, controlp, controlr, controls, execute, exit, give_up, help, locale, qbe_save, qbe_select, select_cancel');
insert into T1 values('CK08','mp700','about, accept, cancel, controlg, controlp, controlr, exit, help, locale, mntn_doc_pty');
insert into T1 values('CK09','mp702','about, accept, cancel, controlg, controlp, controlr, exit, help, locale, mntn_reason');
CREATE TABLE T2 (
AN VARCHAR2(80),
BZ VARCHAR2(20),
ZW VARCHAR2(80)
);
insert into T2 values('about','standard','程式資訊');
insert into T2 values('accept','standard','確定更新');
insert into T2 values('cancel','standard','取消');
insert into T2 values('close','mp002','');
insert into T2 values('close','standard','離開');
insert into T2 values('exit','standard','結束');
insert into T2 values('give_up','standard','放棄');
insert into T2 values('help','standard','幫助');
insert into T2 values('jump','standard','指定筆');
insert into T2 values('last','standard','末一筆');
insert into T2 values('locale','c512','簡易輸入');
insert into T2 values('locale','standard','語言');
insert into T2 values('qbe_save','standard','條件儲存');
需求是從T1的QX在T2表關聯AN 取zw ,條件是當T1.DM=T2.BZ AND T2.ZW IS NOT NULL ，否則standard 對應即可，T2無資料的還是顯示英文。
想要的結果如下：
CK08 mp306 程式資訊,確定更新,取消,開窗查詢,開啟查詢視窗,必要欄位,結束,幫助,簡易輸入,語言,條件儲存,條件查詢
uj5u.com熱心網友回復：
-- 這樣拆分
with m as (
select LX, DM, regexp_substr(QX, '[^,]+',1,level) new_item
from t1 connect by level <= regexp_count(qx,',') + 1
and prior rowid = rowid
and prior dbms_random.value is not null
)
select * from m
uj5u.com熱心網友回復：
太棒了，非常感謝uj5u.com熱心網友回復：
結果確認無誤的話，請及時結貼轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/55469.html
標籤：開發
上一篇：尋求技術指導
下一篇：想知道國內有企業用APEX的嗎