Oracle

組值:CASE 文本、ELSE 欄位值

  • August 3, 2019

我有一張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但是,我不想將其他道路類輸出為,而是獲取它們的欄位值(COLLECTORLOCAL)。

當我嘗試添加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 )

引用自:https://dba.stackexchange.com/questions/172646