SQL 额外的空行

如注释中所述，使用始终为您提供 15 行的帮助程序表。下面的示例是针对 emp/dept 示例数据的，但您也应该能够针对数据集执行此操作。emp 表有 14 行。

步骤 1：生成 15 个 null 行

select level
  from dual
connect by
   level - 1< 15

第 2 步：将行号添加到原始查询中。这将用于联接到帮助程序表。将顺序更改为满足“前 15 名”条件所需的任何内容。

select ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY e.ename),  
       e.ename, 
       e.job 
  from emp e 

现在把它们放在一起：

with dummy_rows (rn) as
(
select level
  from dual
connect by
    level - 1< 15
)
,
my_emp_query(rn, ename, job) as
(
select ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY e.ename),  
       e.ename, 
       e.job 
  from emp e 
)
select d.rn, e.ename, e.job 
  from 
  dummy_rows d
  left outer join my_emp_query e on d.rn = e.rn    ;

        RN ENAME      JOB      
---------- ---------- ---------
         1 ADAMS      CLERK    
         2 ALLEN      SALESMAN 
         3 BLAKE      MANAGER  
         4 CLARK      MANAGER  
         5 FORD       ANALYST  
         6 JAMES      CLERK    
         7 JONES      MANAGER  
         8 KING       PRESIDENT
         9 MARTIN     SALESMAN 
        10 MILLER     CLERK    
        11 SCOTT      ANALYST  
        12 SMITH      CLERK    
        13 TURNER     SALESMAN 
        14 WARD       SALESMAN 
        15                     

15 rows selected. 

注意：第 15 行为空，因为 emp 表中没有匹配的记录。将 15 更改为 5，您将只能获得 5 行。

我成功了！可能 Koen Lostrie 的答案比我所做的要好，但我得到了公司某人的帮助，我认为这最符合我们的标准。代码如下：

SELECT *
    FROM (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'
                 AND ROWNUM <= 15
          UNION ALL
              SELECT NULL     AS IDCODFATURA,
                     NULL     AS PAGINA,
                     NULL     AS TARIFA_RES_TE,
                     NULL     AS VALOR_ICMS_TE,
                     NULL     AS TARIFA_COM_ICMS_TE,
                     NULL     AS TARIFA_SEM_ICMS_TE,
                     NULL     AS VALOR_PISCOFINS_TE,
                     NULL     AS VALOR_COM_ICMS_TE,
                     NULL     AS VALOR_SEM_ICMS_TE,
                     NULL     AS VALOR_BASE_ICMS,
                     NULL     AS TARIFA_TUSD,
                     NULL     AS valor,
                     NULL     AS tarifa,
                     NULL     AS TARIFA_TE,
                     NULL     AS usuario,
                     NULL     AS GUID,
                     NULL     AS TIPO_DESCONTO_MICROGERACAO,
                     NULL     AS VALOR_PIS,
                     NULL     AS VALOR_COFINS
                FROM DUAL
          CONNECT BY LEVEL <=
                     (  15
                      - (SELECT COUNT (*)
                           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')))
   WHERE ROWNUM <= 15
ORDER BY classificacao, sequencia