欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-01 18:26:57
在测试工作中,有时需要测试数据库数据经过sql计算后的结果是否满足某一功能查询得到的返回值。 针对某些需要功能需要联查多张表,此时 关联 的作用就异常重要了,而针对多表关联,其中 关联条件的重要性不言而喻, 不同的关联条件会得到不同的结果集。 废话不多说,下面开始做个实验。 建表 data_stoc ......

在测试工作中,有时需要测试数据库数据经过sql计算后的结果是否满足某一功能查询得到的返回值。

针对某些需要功能需要联查多张表,此时 关联 的作用就异常重要了,而针对多表关联,其中 关联条件的重要性不言而喻,

不同的关联条件会得到不同的结果集。

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

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

建表 data_stock1, data_stock2

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');

 

表data_stock1,select * from data_stock1;

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

表data_stock2,自行插入值吧,数据如下,select * from data_stock2;

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

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

一切准备就绪;下面实验开始;将两个表进行关联,关联 分为 外联,内联。

内联 inner join ,内联 的结果集 是data_stock2,data_stock1表*同存在的结果;data  in (data_stock2,data_stock1)

外联 outer join ,分为全联(full outer join),左联(left join),右联(right join),区别如下:

-- --左关联, 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;

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

-- --内联,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;   

注意,此时出现了 NULL账号,是因为右联的时候,结果集为NULL,而以 group by a.account做聚合,会有NULL

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