Oracle SQL中基于行列优先级的多行多列解码实现问询

现有表结构与数据 #

details表结构及数据如下：

需求说明 #

需要编写Oracle SQL查询，满足以下两个要求：

1. 按color > shading > available的列优先级，为每条记录计算输出值：
   • 若color = 'Y'，直接输出"ABC"（无需检查其他列）
   • 若color = 'N'且shading = 'Y'，输出"XYZ"
   • 若color = 'N'且shading = 'N'且available = 'Y'，输出"Hurray!"
2. 按**ABC > XYZ > Hurray!**的结果优先级，从所有行的输出中获取唯一最终结果。

原有尝试的问题 #

之前使用DECODE的写法无法满足需求，因为它只能逐行单独计算各列的结果，无法实现多条件优先级判断，也无法遍历所有行得到全局唯一的最高优先级结果：

SELECT DECODE (color,'Y','ABC') AS RESULT_color,
DECODE (shading,'Y','XYZ') AS RESULT_shade,
DECODE (available,'Y','Hurray!') AS RESULT_available from details;

解决方案 #

1. 逐行计算结果（使用CASE表达式） #

CASE表达式支持多条件优先级判断，能准确实现每条记录的输出逻辑：

SELECT
  CASE
    WHEN color = 'Y' THEN 'ABC'
    WHEN color = 'N' AND shading = 'Y' THEN 'XYZ'
    WHEN color = 'N' AND shading = 'N' AND available = 'Y' THEN 'Hurray!'
    ELSE NULL -- 不符合条件的记录返回NULL
  END AS record_result
FROM details;

2. 获取全局最高优先级的唯一结果 #

基于逐行计算的结果，通过排序筛选出优先级最高的结果，以下两种方法均可实现：

方法一：使用KEEP DENSE_RANK取最高优先级结果

SELECT MAX(record_result) KEEP (DENSE_RANK FIRST ORDER BY
  CASE record_result
    WHEN 'ABC' THEN 3
    WHEN 'XYZ' THEN 2
    WHEN 'Hurray!' THEN 1
    ELSE 0
  END DESC) AS final_result
FROM (
  SELECT
    CASE
      WHEN color = 'Y' THEN 'ABC'
      WHEN color = 'N' AND shading = 'Y' THEN 'XYZ'
      WHEN color = 'N' AND shading = 'N' AND available = 'Y' THEN 'Hurray!'
      ELSE NULL
    END AS record_result
  FROM details
);

方法二：排序后取第一条非NULL结果

SELECT record_result AS final_result
FROM (
  SELECT DISTINCT
    CASE
      WHEN color = 'Y' THEN 'ABC'
      WHEN color = 'N' AND shading = 'Y' THEN 'XYZ'
      WHEN color = 'N' AND shading = 'N' AND available = 'Y' THEN 'Hurray!'
      ELSE NULL
    END AS record_result
  FROM details
  WHERE record_result IS NOT NULL
  ORDER BY
    CASE record_result
      WHEN 'ABC' THEN 1
      WHEN 'XYZ' THEN 2
      WHEN 'Hurray!' THEN 3
    END
)
WHERE ROWNUM = 1;

结果说明 #

针对示例数据，两种方法的最终结果均为ABC，因为行A的输出ABC优先级最高。

内容的提问来源于stack exchange，提问作者NewToCoding