如何在Oracle查询中复用WHEN条件并优化SQL的效率与可读性？

你的SQL可以优化，核心问题是重复执行了三次几乎一致的子查询，既浪费数据库性能，代码冗余度也高。下面给你几种更高效、可读性更强的纯SQL写法，完全不需要用PL/SQL：

方案一：条件聚合（最优，仅扫描一次表） #

通过一次表扫描，分别计算不同层级的最小值，再用COALESCE优先取层级1的结果：

SELECT COALESCE(
       MIN(CASE WHEN structure_level = 1 THEN handling_unit_id END),
       MIN(CASE WHEN structure_level > 1 THEN handling_unit_id END)
       ) AS HUI
FROM ifsapp.handling_unit_shipment
WHERE shipment_id = '1371'
  AND ifsapp.HANDLING_UNIT_TYPE_API.Get_Handling_Unit_Category_Id(handling_unit_type_id) LIKE 'SST_N1';

逻辑很直接：先筛选出符合条件的所有记录，用CASE分组计算层级1和层级>1的最小ID，最后COALESCE会优先返回第一个非空值，完美匹配你的需求。

方案二：排序优先级法 #

通过排序把层级1的记录排在最前面，再取最小ID：

SELECT MIN(handling_unit_id) AS HUI
FROM (
    SELECT handling_unit_id
    FROM ifsapp.handling_unit_shipment
    WHERE shipment_id = '1371'
      AND ifsapp.HANDLING_UNIT_TYPE_API.Get_Handling_Unit_Category_Id(handling_unit_type_id) LIKE 'SST_N1'
    ORDER BY CASE WHEN structure_level = 1 THEN 0 ELSE 1 END, handling_unit_id
)
FETCH FIRST 1 ROW ONLY;

内层查询先给层级1的记录标记为0、层级>1的标记为1，按这个标记排序后，外层取最小ID就能优先拿到层级1的结果。

关于你提到的「把WHEN条件存到变量」 #

纯SQL里可以用WITH子句定义公共表达式，相当于把筛选后的数据集临时存储，避免重复写条件，可读性更强：

WITH filtered_data AS (
    SELECT handling_unit_id, structure_level
    FROM ifsapp.handling_unit_shipment
    WHERE shipment_id = '1371'
      AND ifsapp.HANDLING_UNIT_TYPE_API.Get_Handling_Unit_Category_Id(handling_unit_type_id) LIKE 'SST_N1'
)
SELECT COALESCE(
       MIN(CASE WHEN structure_level = 1 THEN handling_unit_id END),
       MIN(CASE WHEN structure_level > 1 THEN handling_unit_id END)
       ) AS HUI
FROM filtered_data;

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