各位大侠:
我有一个多语言翻译的表 T ,其数据格式格式如下:TRANS_CODE,TRANS_NAME, LANGUAGE_CODE
M1 M1(中文) ZHS
M1 M1(英文) ENG
M2 M2(中文) ZHS
M2 M2(英文) ENG
M3 M3(中文) ZHS
M3 M3(英文) ENG
M4 M4(中文) ZHS
M4 M4(英文) ENG
M5 M5(中文) ZHS
......需求是:我现在要用一条SQL取出所有LANGUAGE_CODE='ENG'的所有TRANS_CODE,TRANS_NAME
如果有TRANS_CODE没有英文的数据,则用中文的数据代替!如:假如取的SQL是:SELECT TRANS_CODE,TRANS_NAME FROM T WHERE T.LANGUAGE_CODE='ENG'那么结果应该是:
TRANS_CODE,TRANS_NAME
M1 M1(英文)
M2 M2(英文)
M3 M3(英文)
M4 M4(英文)
M5 M5(中文)这个SQL该怎么写呢?OracleSQL编程语言
我有一个多语言翻译的表 T ,其数据格式格式如下:TRANS_CODE,TRANS_NAME, LANGUAGE_CODE
M1 M1(中文) ZHS
M1 M1(英文) ENG
M2 M2(中文) ZHS
M2 M2(英文) ENG
M3 M3(中文) ZHS
M3 M3(英文) ENG
M4 M4(中文) ZHS
M4 M4(英文) ENG
M5 M5(中文) ZHS
......需求是:我现在要用一条SQL取出所有LANGUAGE_CODE='ENG'的所有TRANS_CODE,TRANS_NAME
如果有TRANS_CODE没有英文的数据,则用中文的数据代替!如:假如取的SQL是:SELECT TRANS_CODE,TRANS_NAME FROM T WHERE T.LANGUAGE_CODE='ENG'那么结果应该是:
TRANS_CODE,TRANS_NAME
M1 M1(英文)
M2 M2(英文)
M3 M3(英文)
M4 M4(英文)
M5 M5(中文)这个SQL该怎么写呢?OracleSQL编程语言
SELECT 'M1' TRANS_CODE,'M1(中文)' TRANS_NAME from dual UNION
SELECT 'M1' TRANS_CODE,'M1(英文)' TRANS_NAME from dual UNION
SELECT 'M2' TRANS_CODE,'M2(中文)' TRANS_NAME from dual UNION
SELECT 'M2' TRANS_CODE,'M2(英文)' TRANS_NAME from dual UNION
SELECT 'M3' TRANS_CODE,'M3(中文)' TRANS_NAME from dual UNION
SELECT 'M3' TRANS_CODE,'M3(英文)' TRANS_NAME from dual UNION
SELECT 'M4' TRANS_CODE,'M4(中文)' TRANS_NAME from dual)
SELECT * FROM (
SELECT TRANS_CODE,TRANS_NAME,ROW_NUMber()over(PARTITION BY TRANS_CODE order by TRANS_NAME) AS RN from t )WHERE RN=1
是不是要这么个样子
case when regexp_like(TRANS_NAME, '.([a-z]+|[A-Z])') then t.trans_name else '中文' end
FROM T t
WHERE T.LANGUAGE_CODE = 'ENG'
select b.TRANS_CODE, nvl(a.TRANS_NAME, b.TRANS_NAME) from t a, t b where a.trans_code(+)=b.trans_code
and a.LANGUAGE_CODE(+) = 'ENG' and b.LANGUAGE_CODE='ZHS'
结果如下:
M1|M1(英文)
M2|M2(英文)
M3|M3(英文)
M4|M4(英文)
M5|M5(中文)