SQL相关

aaa表

bbb表

CREATE TABLE gp20i17mp_gi.bbb (
	product varchar(255) NULL,
	reporting_dt varchar(255) NULL
)
DISTRIBUTED BY (product);

INSERT INTO gp20i17mp_gi.bbb (product,reporting_dt) VALUES 
('p1','20201231')
,('p1','20201130')
,('p2','20200831')
;

CREATE TABLE gp20i17mp_gi.aaa (
	product varchar(10) NULL,
	reporting_dt varchar(255) NULL,
	age int4 NULL,
	sale int2 NOT NULL
)
DISTRIBUTED BY (product);

INSERT INTO gp20i17mp_gi.aaa (product,reporting_dt,age,sale) VALUES 
('p1','20201231',7,7)
,('p1','20201130',6,8)
,('p1','20201231',8,6)
,('p3','20201231',10,3)
;

And 做筛选条件

select * from gp20i17mp_gi.aaa a 
left join gp20i17mp_gi.bbb b 
on a.product = b.product 

主表筛选

select * from gp20i17mp_gi.aaa a 
left join gp20i17mp_gi.bbb b 
on a.product = b.product 
and a.reporting_dt ='20201130'
// on a.product = b.product 如果a.product是null,null是不匹配任何值的，所以a.product=null的这个记录
// 是匹配不到b的

关联表筛选

select * from gp20i17mp_gi.aaa a 
left join gp20i17mp_gi.bbb b 
on a.product = b.product 
and b.reporting_dt ='20201130'

select * from gp20i17mp_gi.aaa a
inner join gp20i17mp_gi.bbb b
on a.product = b.product 
and a.reporting_dt = '20201231'

select * from gp20i17mp_gi.aaa a
inner join gp20i17mp_gi.bbb b
on a.product = b.product 
and b.reporting_dt = '20201231'

insert

bbb的数据

所以以下两种写法正确


以下顺序也是错的，都是先重select字段名，然后按照顺序放到gp20i17mp_gi.bbbb(reporting_dt, product )里

drop table if exists gp20i17mp_gi.bbbb;
create table gp20i17mp_gi.bbbb(reporting_dt, product )
as select product,reporting_dt from gp20i17mp_gi.aa;
select * from gp20i17mp_gi.bbbb;

所以可用aa的表结构放到bbbb里，然后写select *

drop table if exists gp20i17mp_gi.bbbb;
create table gp20i17mp_gi.bbbb( product,reporting_dt )
as select * from gp20i17mp_gi.aa;
select * from gp20i17mp_gi.bbbb;