22,209
社区成员
发帖
与我相关
我的任务
分享
insert overwrite table result_table
select report_date,sum(purchase),sum(redeem) from (
select report_date,purchase,redeem from poor_table
union all
select report_date,purchase,redeem from rich_table)
group by report_date
--参考下:
select report_date,purchase,redeem into result_table from (select p.report_date as report_date,p.purchase+r.purchase as purchase,p.redeem+r.redeem as redeem from poor_table p join rich_table r on p.report_date=r.report_date)t
--创建表poor_table
create table poor_table
(
report_date varchar(30),
purchase int,
redeem int
)
--向poor_table表中插入数据
insert into poor_table
select '0101' ,'23', '21' union all
select '0102' , '11','31' union all
select '0103' , '10','22' union all
select '0104' ,'7','11'
--创建表rich_table
create table rich_table
(
report_date varchar(30),
purchase int,
redeem int
)
--向rich_table表中插入数据
insert into rich_table
select '0101' ,'123', '121' union all
select '0102' , '101','131' union all
select '0103' , '101','220' union all
select '0104' ,'74','111'
--创建表result_table
create table result_table
(
report_date varchar(30),
purchase int,
redeem int
)
insert into result_table
select a.report_date,sum(a.purchase),sum(a.redeem) from
(
select report_date,purchase,redeem from poor_table
union all
select report_date,purchase,redeem from rich_table
)a group by a.report_date
select * from result_table