SQL语句实现按照分组划分优先级

SQL语句实现按照分组划分优先级

-------分组划分优先级
SELECT B.*
  FROM (SELECT A.color,
               A.P,
               row_number() over(partition by P order by color desc) rn
          FROM (SELECT 'red' AS color, '3' AS P
                  FROM DUAL
                UNION
                SELECT 'red' AS color, '3' AS P
                  FROM DUAL
                UNION
                SELECT 'black' AS color, '3' AS P
                  FROM DUAL
                UNION
                SELECT 'yellow' AS color, '2' AS P
                  FROM DUAL
                UNION
                SELECT 'green' AS color, '2' AS P
                  FROM DUAL
                UNION
                SELECT 'blue' AS color, '1' AS P FROM DUAL) A) B
 WHERE rn < 2

结果截图：