SQL查询结果补全空行并限制15行的实现需求咨询

嘿，我来帮你搞定这个需求！你想要让SQL查询刚好返回15行——原数据够15行就取前15，不够的话用空行（null值）补满，对吧？你的现有查询已经能处理“超过15行取前15”的情况，但还缺少补空行的逻辑，我来给你调整一下。

实现思路 #

核心思路是生成一个包含1到15连续行号的辅助数据集，然后和你的查询结果做左连接：

• 先把你的原有查询处理成带行号的结果集，只取前15行；
• 生成1到15的行号作为“占位行”；
• 左连接两个数据集，这样占位行里没有匹配到原数据的部分就会显示null，最终刚好返回15行。

适配Oracle 12c+的完整代码 #

WITH numbered_data AS (
    -- 原查询加上行号，取前15行
    SELECT 
        rownum AS row_num,
        IDCODFATURA,
        PAGINA,
        TARIFA_RES_TE,
        VALOR_ICMS_TE,
        TARIFA_COM_ICMS_TE,
        TARIFA_SEM_ICMS_TE,
        VALOR_PISCOFINS_TE,
        VALOR_COM_ICMS_TE,
        VALOR_SEM_ICMS_TE,
        VALOR_BASE_ICMS,
        TARIFA_TUSD,
        CASE
            WHEN NVL(param_com_tributos.tipo_imp_imposto_fat, 0) = 0 THEN base_fatura_itens.valor_com_icms
            ELSE base_fatura_itens.VALOR_SEM_PCICMS
        END AS valor,
        CASE
            WHEN NVL(param_com_tributos.tipo_imp_imposto_fat, 0) = 0 THEN base_fatura_itens.tarifa_com_icms
            ELSE base_fatura_itens.tarifa_res
        END AS tarifa,
        TARIFA_TE,
        usuario,
        GUID,
        TIPO_DESCONTO_MICROGERACAO,
        (SELECT VALOR_PIS
         FROM base_fatura_web
         WHERE IDCODFATURA = :IDCODFATURA
           AND PAGINA = :PAGINA
           AND GUID = :GUID
           AND usuario = :usuario) AS VALOR_PIS,
        (SELECT VALOR_COFINS
         FROM base_fatura_web
         WHERE IDCODFATURA = :IDCODFATURA
           AND PAGINA = :PAGINA
           AND GUID = :GUID
           AND usuario = :usuario) AS VALOR_COFINS
    FROM base_fatura_itens, param_com_tributos
    WHERE IDCODFATURA = :IDCODFATURA
      AND PAGINA = :PAGINA
      AND param_com_tributos.idparam = 1
      AND base_fatura_itens.GUID = :GUID
      AND base_fatura_itens.usuario = :usuario
      AND base_fatura_itens.descricao <> 'ICMS Descontos tarifários'
    ORDER BY classificacao, sequencia
    FETCH FIRST 15 ROWS ONLY -- Oracle 12c+支持的语法，更清晰地取前15行
),
rows_1_to_15 AS (
    -- 生成1到15的连续行号作为占位行
    SELECT LEVEL AS row_num
    FROM dual
    CONNECT BY LEVEL <= 15
)
-- 左连接确保返回15行，不足的行自动补null
SELECT 
    nd.IDCODFATURA,
    nd.PAGINA,
    nd.TARIFA_RES_TE,
    nd.VALOR_ICMS_TE,
    nd.TARIFA_COM_ICMS_TE,
    nd.TARIFA_SEM_ICMS_TE,
    nd.VALOR_PISCOFINS_TE,
    nd.VALOR_COM_ICMS_TE,
    nd.VALOR_SEM_ICMS_TE,
    nd.VALOR_BASE_ICMS,
    nd.TARIFA_TUSD,
    nd.valor,
    nd.tarifa,
    nd.TARIFA_TE,
    nd.usuario,
    nd.GUID,
    nd.TIPO_DESCONTO_MICROGERACAO,
    nd.VALOR_PIS,
    nd.VALOR_COFINS
FROM rows_1_to_15 r
LEFT JOIN numbered_data nd ON r.row_num = nd.row_num
ORDER BY r.row_num;

适配Oracle 12c以下版本的代码 #

如果你的Oracle版本低于12c，不支持FETCH FIRST语法，可以把numbered_data部分调整成嵌套查询的方式，确保排序后再取前15行：

WITH numbered_data AS (
    SELECT 
        rownum AS row_num,
        t.*
    FROM (
        -- 原查询保持不变，先排序再取rownum
        SELECT 
            IDCODFATURA,
            PAGINA,
            TARIFA_RES_TE,
            VALOR_ICMS_TE,
            TARIFA_COM_ICMS_TE,
            TARIFA_SEM_ICMS_TE,
            VALOR_PISCOFINS_TE,
            VALOR_COM_ICMS_TE,
            VALOR_SEM_ICMS_TE,
            VALOR_BASE_ICMS,
            TARIFA_TUSD,
            CASE
                WHEN NVL(param_com_tributos.tipo_imp_imposto_fat, 0) = 0 THEN base_fatura_itens.valor_com_icms
                ELSE base_fatura_itens.VALOR_SEM_PCICMS
            END AS valor,
            CASE
                WHEN NVL(param_com_tributos.tipo_imp_imposto_fat, 0) = 0 THEN base_fatura_itens.tarifa_com_icms
                ELSE base_fatura_itens.tarifa_res
            END AS tarifa,
            TARIFA_TE,
            usuario,
            GUID,
            TIPO_DESCONTO_MICROGERACAO,
            (SELECT VALOR_PIS
             FROM base_fatura_web
             WHERE IDCODFATURA = :IDCODFATURA
               AND PAGINA = :PAGINA
               AND GUID = :GUID
               AND usuario = :usuario) AS VALOR_PIS,
            (SELECT VALOR_COFINS
             FROM base_fatura_web
             WHERE IDCODFATURA = :IDCODFATURA
               AND PAGINA = :PAGINA
               AND GUID = :GUID
               AND usuario = :usuario) AS VALOR_COFINS
        FROM base_fatura_itens, param_com_tributos
        WHERE IDCODFATURA = :IDCODFATURA
          AND PAGINA = :PAGINA
          AND param_com_tributos.idparam = 1
          AND base_fatura_itens.GUID = :GUID
          AND base_fatura_itens.usuario = :usuario
          AND base_fatura_itens.descricao <> 'ICMS Descontos tarifários'
        ORDER BY classificacao, sequencia
    ) t
    WHERE rownum <= 15
),
rows_1_to_15 AS (
    SELECT LEVEL AS row_num
    FROM dual
    CONNECT BY LEVEL <= 15
)
SELECT 
    nd.IDCODFATURA,
    nd.PAGINA,
    nd.TARIFA_RES_TE,
    nd.VALOR_ICMS_TE,
    nd.TARIFA_COM_ICMS_TE,
    nd.TARIFA_SEM_ICMS_TE,
    nd.VALOR_PISCOFINS_TE,
    nd.VALOR_COM_ICMS_TE,
    nd.VALOR_SEM_ICMS_TE,
    nd.VALOR_BASE_ICMS,
    nd.TARIFA_TUSD,
    nd.valor,
    nd.tarifa,
    nd.TARIFA_TE,
    nd.usuario,
    nd.GUID,
    nd.TIPO_DESCONTO_MICROGERACAO,
    nd.VALOR_PIS,
    nd.VALOR_COFINS
FROM rows_1_to_15 r
LEFT JOIN numbered_data nd ON r.row_num = nd.row_num
ORDER BY r.row_num;

代码说明 #

• numbered_data：把你的原查询包装起来，添加行号并限制前15行，保证只取排序后的前15条有效数据；
• rows_1_to_15：用Oracle的递归查询生成1到15的连续行号，作为补空行的“占位符”；
• 最后的左连接会把占位行和有效数据关联，没有匹配到有效数据的占位行就会返回null，最终结果刚好是15行。

备注：内容来源于stack exchange，提问作者Luana Hilbert