# 分分钟 搞懂 各种类型的关联

废话不多说，下面开始做个实验。

drop table if exists data_stock1;
drop table if exists data_stock2;

-- 区分二表，通过amount取不同字段

create table data_stock1(
account varchar(20),
amount1 int(10),
init_date varchar(10)
);

create table data_stock1(
account varchar(20),
amount2 int(10),
init_date varchar(10)
);

insert into data_stock1(account,amount1,init_date) values('2001',200,'20170101');
insert into data_stock1 (account,amount1,init_date) values('2001',30,'20170102');
insert into data_stock1 (account,amount1,init_date) values('2002',210,'20170102');
insert into data_stock1 (account,amount1,init_date) values('2003',70,'20170102');insert into data_stock1(account,amount1,init_date) values('2002',10,'20170101');

---------------------------------------------------我是分割线-------------------------------------

-- --左关联, a.account=b.account

select *

from data_stock1 a left join data_stock2 b on (a.account=b.account);

-- --左关联,a.account=b.account and a.init_date=b.init_date
select *
from data_stock1 a left join data_stock2 b on (a.account=b.account and a.init_date=b.init_date);

-- --左关联,a.account=b.account,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a left join data_stock2 b on (a.account=b.account )
group by a.account;

-- --左关联,a.account=b.account,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a left join data_stock2 b on (a.account=b.account and a.init_date=b.init_date)
group by a.account;

-- --右关联, a.account=b.account
select *
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account);

-- --右关联,a.account=b.account and a.init_date=b.init_date
select *
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account and a.init_date=b.init_date);

-- --内联, a.account=b.account

--写法1 ，select *
from data_stock1 a INNER JOIN data_stock2 b on a.account=b.account;

--写法2， select *
from data_stock1 a , data_stock2 b where a.account=b.account;

--写法3 ，select *
from data_stock1 a JOIN data_stock2 b on a.account=b.account;

-- --内联,a.account=b.account and a.init_date=b.init_date
select *
from data_stock1 a INNER JOIN data_stock2 b on (a.account=b.account and a.init_date=b.init_date);

select *
from data_stock1 a ,data_stock2 b where (a.account=b.account and a.init_date=b.init_date);

-- --右关联,a.account=b.account,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account )
group by a.account;

-- --右关联,a.account=b.account,再做聚合 求平均值,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account and a.init_date=b.init_date)
group by a.account;

--- 多表关联探究

posted @ 2018-04-03 14:55  Code~Rush  阅读(499)  评论(0编辑  收藏  举报