SQL如何将两张表中相同列名的数据相加

luolingzhichen 2015-07-09 01:32:05
比如 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
...全文
3689 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Cherise_huang 2015-07-09
from 后面应该是表名啊,需要将经过处理查出来的数据封装成一张表,我们从这张表中在查询我们需要的数据
回复
luolingzhichen 2015-07-09
引用 1 楼 Cherise_huang 的回复:

--创建表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
附上图片
原来对表进行命名,为什么要命名呢
回复
粗看好像没什么错误,给加一个别称a,你试试这个: 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) a group by report_date
回复
Neo_whl 2015-07-09

--参考下:
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
回复
Cherise_huang 2015-07-09

--创建表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


附上图片
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-09 01:32
社区公告
暂无公告