主页 / 问题 / 1403979
Accepted
Alex Konkin
Asked:2022-06-26 20:03:05 +0000 UTC2022-06-26 20:03:05 +0000 UTC

按级别执行连接时重复

  • 772

请告诉我如何更改查询，以便最终级别不重复行，即执行后有 8 行，而不是 30 行。例如，您可以写 distinct，但查询需要很长时间是时候处理大数据了。

with cte as 
(select 1 as first_ticket_barcode
from dual

union all

select 5 as first_ticket_barcode
from dual
)

select first_ticket_barcode + level -1
from cte
connect by level <= 4
plsql connect

1 个回答

  1. Best Answer

    connect by 根据 connect by 中的条件将选择中的所有行相互粘合，并且由于没有连接条件，因此所有行彼此相乘。为了防止这种情况发生，您需要制作一个单独的必要值生成器\u200b\u200band 将其结果与输入数据一起粘贴：

    with cte as (
    select 1 as first_ticket_barcode from dual
    union all
    select 5 as first_ticket_barcode from dual
)
select first_ticket_barcode + lev - 1
from cte,
     (select level lev from DUAL connect by level <= 4) gen
    • 1

相关问题