循环遍历表1，从表2中提取信息，并在Teradata中插入到表3中

Question

我是SQL中控制结构的新手，我正在尝试循环浏览一个包含客户和日期信息的表，并根据日期列查找该客户在未来7天内的销售额，然后将其插入另一个表中。以下是我目前所掌握的内容，但我认为set语句不适用于select：

create procedure proc()
for cust_cursor as cc cursor for 
select customer, date_dt from table1
do
set sales = (
select sum(sales_amt) from table2 
where customer = cust_cursor.customer and date_dt between cust_cursor.date_dt and (interval '7' day + cust_cursor.date_dt)
)
insert into table3 values (cust_cursor.customer, sales)
end for;
call proc();

Answer 1

Fred的评论全文：-(

但要解决您的特定问题，请使用SELECT INTO而不是SET

do
select sum(sales_amt) INTO sales from table2 
where customer = cust_cursor.customer and date_dt between cust_cursor.date_dt and (interval '7' day + cust_cursor.date_dt)

注意；循环通过"；通常应转换为仅"；加入">

insert into table3 (cust_cursor.customer, sales)
select t1.customer, sum(t2.sales_amt) 
from table1 as t1
join table2 as t2
on t2.customer = t1.customer
and t2.date_dt between t1.date_dt and (interval '7' day + t1.date_dt)
;

看马，没有循环，没有进程，效率更高。