Oracle
組值:CASE 文本、ELSE 欄位值
我有一張
ROAD
桌子:+----+------------+ | ID | ROAD_CLASS | +----+------------+ | 1 | ARTERIAL A | | 2 | ARTERIAL B | | 3 | ARTERIAL B | | 4 | ARTERIAL C | | 5 | ARTERIAL C | | 6 | ARTERIAL C | | 7 | COLLECTOR | | 8 | COLLECTOR | | 9 | LOCAL | | 10 | LOCAL | +----+------------+
該
ROAD_CLASS
欄位的數據類型是NVARCHAR2
。我想創建一個視圖,將所有主幹道歸為一個
ARTERIAL
類別,但保留其他道路類別:+------------+ | ROAD_CLASS | +------------+ | ARTERIAL | | COLLECTOR | | LOCAL | +------------+
我怎樣才能做到這一點?
我試過的:
我已經成功完成了其中的一部分:
SELECT (CASE WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL' WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL' WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL' --ELSE ROAD_CLASS END) AS ROAD_CLASS_GROUPED FROM USER.ROAD GROUP BY CASE WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL' WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL' WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL' END +--------------------+ | ROAD_CLASS_GROUPED | +--------------------+ | null | | ARTERIAL | +--------------------+
null
但是,我不想將其他道路類輸出為,而是獲取它們的欄位值(COLLECTOR
和LOCAL
)。當我嘗試添加
ELSE ROAD_CLASS
到 CASE 語句(如 SQL 註釋中所示)時,我收到一個ORA-12704: character set mismatch
錯誤。
怎麼樣 …
select distinct (case when road_class in('ARTERIAL A','ARTERIAL B','ARTERIAL C') then n'ARTERIAL' else road_class end ) as ROAD_CLASS_GROUPED from user.road;
由於 road_class 列的數據類型是 NVARCHAR,我們需要在字元串文字 ‘ARTERIAL’ 前面寫一個 N 或 n 以防止出現錯誤消息。(另見:https ://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218 )