我有一個如下表，我想從該表中的所有排列中回傳最大值為最后 4 位的不同服務器名稱：
| 服務器名稱 |
|---|
| APPQTV1234 |
| IISLUG60DF |
| XCCPTV401D |
所以基本上我可以使用以下查詢獲得唯一組合：
select DISTINCT SUBSTR(ServerName,1,6) from MYTABLE;
這給了我：
| 服務器名稱 |
|---|
| APPQTV |
| IISLUG |
| 中共中央電視臺 |
現在我希望查詢回傳最后 4 位的最大值，這在 HEX 中基本上是增量的
任何建議都會有所幫助！提前致謝
編輯 1 預期結果如下：
所以如果表有這些值：
| 服務器名稱 |
|---|
| APPQTV1234 |
| IISLUG6578 |
| XCCPTV7894 |
| APPQTV4321 |
| IISLUG9999 |
| XCCPTV8049 |
然后查詢應該回傳每個組合的最大值。例如：
| 服務器名稱 |
|---|
| APPQTV4321 |
| IISLUG9999 |
| XCCPTV8049 |
uj5u.com熱心網友回復：
我們可以使用 'XX' 格式掩碼to_number()將十六進制轉換為十進制。X 的數量必須與十六進制字串中的字符數匹配。然后是一個簡單的聚合。
with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'APPQTV1C34' as servername from dual union all
select 'IISLUG60DF' as servername from dual union all
select 'IISLUG80DF' as servername from dual union all
select 'XCCPTV401D' as servername from dual
)
select SUBSTR(ServerName,1,6) as server_name
,max(to_number(substr(servername, 7, 4), 'xxxx')) as server_no
from MYTABLE
group by SUBSTR(ServerName,1,6)
替代解決方案：如果您的 NLS_SORT 引數設定為binary（即按 ASCII 值排序），字母數字排序規則將以遞增值對十六進制數字進行排序。所以你可以這樣做：
with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'APPQTVA234' as servername from dual union all
select 'APPQTV1C34' as servername from dual union all
select 'IISLUG6578' as servername from dual union all
select 'IISLUG80DF' as servername from dual union all
select 'APPQTV4321' as servername from dual union all
select 'IISLUG9999' as servername from dual union all
select 'XCCPTV8049' as servername from dual union all
select 'XCCPTV401D' as servername from dual
)
select SUBSTR(ServerName,1,6)
|| max(substr(servername, 7, 4)) as max_server_name
from MYTABLE
group by SUBSTR(ServerName,1,6)
db<>fiddle上的演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472551.html
上一篇：在Where子句中的Oracle日期給我這個錯誤ORA-00932：不一致的資料型別：預期的NUMBER得到了DATE