从multipe循环插入记录

c2e8gylq 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(328)

我有下面的查询，可以正常工作。如何修改此查询以将记录插入下表。

with loop1(tour_id) as (select level from
 dual connect by level <= 10)
    ,loop2(b) as (select level from dual connect by level <= 7)
SELECT tour_id,
    'Tour ' || tour_id AS tour_name,

replace(listagg(CASE round(dbms_random.value(1,2))

WHEN 1 THEN 'Y'

WHEN 2 THEN 'N'

END,',')               
within group (order by b)
             ,',') AS active_days
  FROM  loop1
      cross join loop2
 group by tour_id

create table tour_hdr(
  tour_id NUMBER(4),
   tour_name VARCHAR2(20),
  active_days VARCHAR2(7)
)

sql oracle Insert

来源：https://stackoverflow.com/questions/62540243/insert-records-from-multipe-loops

1条答案

按热度按时间

mec1mxoz1#

如何将此查询修改为 INSERT 记录到下表？
这样地：

insert into tour_hdr (tour_id, tour_name, active_days)
with 
  loop1 (tour_id) as (select ...),
  loop2 (b)       as (select ...)
select tour_id,
       'Tour ' ||tour_id as tour_name,
       replace(listagg(...)) as active_days
from loop1 cross join loop2
group by tour_id;

赞(0）回复(0）举报 2021-07-24

我来回答

从multipe循环插入记录

1条答案

相关问题

热门标签

最新问答