比如 poor_table
report_date purchase redeem
0101 23 21
0102 11 31
0103 10 22
0104 7 11
rich_table
report_date purchase redeem
0101 123 121
0102 101 131
0103 101 220
0104 74 111
结果
result_table
report_date purchase redeem
0101 146 142
0102 112 162
0103 111 242
0104 81 122
我的代码
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
我是在ODPS SQL 上跑的语法和Hive一样，报的错
Parse exception - line 6:0 mismatched input 'group' expecting Identifier near ')' in subquery source
------解决思路----------------------
--创建表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
附上图片
