Oracle SQL子查询如何返回多个字段值 简化重复代码的实现方法

问题原因 #

你尝试的写法不生效，是因为Oracle的标量子查询仅支持返回单个行单个列的结果，不能直接通过一次子查询给多个字段批量赋值。你原来重复写多次子查询的写法虽然可运行，但代码冗余度高，且同一份过滤逻辑重复执行多次，执行效率很低。

方案1：JOIN关联聚合结果（兼容性最高，适配所有Oracle版本） #

先通过子查询提前算出每个numero对应的最大seq，再一次关联原表取出所有需要的字段，避免重复查询：

SELECT
    a.GESTOR,
    a.TIPO,
    a.NUMERO,
    b.seq,
    b.data,
    b.valor,
    b.cod_nloc,
    b.usuario
FROM
    (
        -- 先分组拿到需要的GESTOR、TIPO、NUMERO维度
        SELECT GESTOR, TIPO, NUMERO
        FROM SM_HISTPATR_SE90
        WHERE DATA <= '01/01/2020' 
          AND MOVIMENTO = 'E' 
          AND numero = 66480
        GROUP BY GESTOR, TIPO, NUMERO
    ) a
    -- 关联原表取对应字段
    JOIN SM_HISTPATR_SE90 b ON b.numero = a.numero
    -- 关联提前计算好的每个numero的最大seq
    JOIN (
        SELECT numero, max(seq) max_seq
        FROM SM_HISTPATR_SE90
        WHERE movimento = 'E'
        GROUP BY numero
    ) c ON b.numero = c.numero AND b.seq = c.max_seq

方案2：使用ROW_NUMBER分析函数（性能最优，仅需一次表扫描） #

用窗口函数直接给每个numero的行按seq倒序排序，取排序第一的行即可，代码更简洁：

SELECT 
    GESTOR,
    TIPO,
    NUMERO,
    seq,
    data,
    valor,
    cod_nloc,
    usuario
FROM (
    SELECT
        GESTOR,
        TIPO,
        NUMERO,
        seq,
        data,
        valor,
        cod_nloc,
        usuario,
        -- 按numero分组，seq倒序排序，最大的seq对应rn=1
        ROW_NUMBER() OVER(PARTITION BY numero ORDER BY seq DESC) rn
    FROM SM_HISTPATR_SE90
    WHERE DATA <= '01/01/2020' 
      AND MOVIMENTO = 'E' 
      AND numero = 66480
) t
WHERE rn = 1
-- 若存在多个相同GESTOR、TIPO、NUMERO的行，可保留原GROUP BY逻辑
GROUP BY GESTOR, TIPO, NUMERO, seq, data, valor, cod_nloc, usuario

注意：示例中保留了你原有的日期写法，实际使用建议用TO_DATE('01/01/2020','DD/MM/YYYY')明确指定日期格式，避免因数据库默认日期格式不同导致执行报错。

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