查詢以考慮具有優先級的分組的型別

我需要根據它們的型別對一些資料進行分組并考慮優先級。

以下面的 CTE 為例。

WITH classif AS
(
    select 1 as id, 'account' as type, 'high' as priority from dual union all
    select 2 as id, 'account' as type, 'none' as priority from dual union all
    select 3 as id, 'account' as type, 'medium' as priority from dual union all
    select 4 as id, 'security' as type, 'high' as priority from dual union all
    select 5 as id, 'security' as type, 'medium' as priority from dual union all
    select 6 as id, 'security' as type, 'low' as priority from dual union all
    select 7 as id, 'security' as type, 'none' as priority from dual union all
    select 8 as id, 'transform' as type, 'none' as priority from dual union all
    select 9 as id, 'transform' as type, 'none' as priority from dual union all
    select 10 as id, 'transform' as type, 'none' as priority from dual union all
    select 11 as id, 'transform' as type, 'none' as priority from dual union all
    select 12 as id, 'enrollment' as type, 'medium' as priority from dual union all
    select 13 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 14 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 15 as id, 'enrollment' as type, 'low' as priority from dual;
    select 15 as id, 'process' as type, 'low' as priority from dual;
    select 15 as id, 'process' as type, 'none' as priority from dual;
    select 15 as id, 'process' as type, 'none' as priority from dual;
)

對于這個資料集，我的輸出必須是這樣的

------------ -------------
type        |  priority
------------ -------------
account     |  high
security    |  high
transform   |  none
enrollment  |  medium
process     |  low
---------------------------

優先級從“高”到“無”

輸出的規則必須是這樣的

• 當型別具有優先級為“高”的行時，該型別的輸出必須為“高”。
• 當一個型別有一行優先級為“中”而沒有其他優先級為“高”時，輸出必須為“中”。
• 當一個型別有一個優先級為“低”的行而沒有其他優先級為“高”或“中”時，輸出必須為“低”。
• 當一個型別有一行優先級為“none”并且沒有任何其他時，輸出必須是“none”

我正在嘗試執行類似下面的查詢，但這將回傳所有行，而不是根據優先級分組

select 
    type,
    case 
        when priority = 'high' then 'high'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
        when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
        end as priority        
from classif
group by type,
    case
    when priority = 'high' then 'high'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
        when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
        end;

你能幫我在查詢中解決這個問題嗎？

uj5u.com熱心網友回復：

您可以使用ROW_NUMBER和 order byCASE陳述句將優先級轉換為數值：

SELECT id, type, priority
FROM   (
  SELECT c.*,
         ROW_NUMBER() OVER (
           PARTITION BY type
           ORDER BY CASE priority
                    WHEN 'high'   THEN 1
                    WHEN 'medium' THEN 2
                    WHEN 'low'    THEN 3
                                  ELSE 4
                    END
         ) AS rn
  FROM   classif c
)
WHERE rn = 1;

或者，您可以使用陳述句MAX(...) KEEP (DENSE RANK FIRST ...)類似地使用和排序CASE：

SELECT MAX(id) KEEP (
         DENSE_RANK FIRST
         ORDER BY CASE priority
                  WHEN 'high'   THEN 1
                  WHEN 'medium' THEN 2
                  WHEN 'low'    THEN 3
                                ELSE 4
                  END
       ) AS id,
       type,
       MAX(priority) KEEP (
         DENSE_RANK FIRST
         ORDER BY CASE priority
                  WHEN 'high'   THEN 1
                  WHEN 'medium' THEN 2
                  WHEN 'low'    THEN 3
                                ELSE 4
                  END
       ) AS priority
FROM   classif
GROUP BY type

或者，您可以使用子查詢來存盤相對優先級并使用連接：

SELECT MAX(c.id) KEEP (DENSE_RANK LAST ORDER BY p.id) AS id,
       type,
       MAX(c.priority) KEEP (DENSE_RANK LAST ORDER BY p.id) AS priority
FROM   classif c
       LEFT OUTER JOIN (
         SELECT 'high' AS priority, 3 As id FROM DUAL UNION ALL
         SELECT 'medium', 2 FROM DUAL UNION ALL
         SELECT 'low',    1 FROM DUAL UNION ALL
         SELECT 'none',   0 FROM DUAL
       ) p
       ON c.priority = p.priority
GROUP BY c.type

其中，對于您的示例資料，所有輸出：

ID	型別	優先事項
1	帳戶	高的
12	注冊	中等的
15	程序	低的
4	安全	高的
8	轉變	沒有任何

db<>在這里擺弄

標籤：sql 甲骨文 每组最大 n

上一篇：如何在Oracle中將`(12345)some_string`的一列字串拆分為兩列`12345`、`some_string`

下一篇：LNNVL替換NOTIN？