【无标题】天池机器学习task4

集合运算-表的加减法和join等
1、表的加减法
集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合。具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

 一：加法：union
1、表的加法：union,并集，但是会去重，就是去重重复的记录。

select * from A union select * from B;

2、union集合运算要求有相同的列，如果一个表中没有另一个表的列，那么会发生错误
3、使用union时，order by 只可在语句的最后使用。
4、如果想包含重复的记录，只要在union后面添加ALL即可。
例如

select * from A union all select * from B;

2、连结(JOIN)

2.1 内连结(INNER JOIN)

内连接，也叫等值连接， inner join 得出同时存在 t_01 和 t_02 的数据集，通俗一点说就是求两个表的
交集。

 

2.2 左连接

左连接： left [outer] join,左连接从左表(t_01)取出所有记录,与右表(t_02)匹配。如果没有匹配，以 null 值代表
右边表的列。 outer 可以不写，默认情况下不写 outer 关键字。

2.3 右连结

右连接： right [outer] join,右连接从右表(t_02)取出所有记录,与左表(t_01)匹配。如果没有匹配,以 null 值代表
左边表的列。

2.4 全连接

MySQL 暂不支持这种语句，不过可以使用 union 将两个结果集“堆一起”原理，利用左连接，右连接分两
次将数据取出， 然后用 union 将数据合并去重。

 

练习题

1、找出 product 和 product2 中售价高于 500 的商品的基本信息

select * from
product where sale_price > 500
union
select * from 
product2 where sale_price > 500;

 

2、借助对称差的实现方式, 求product和product2的交集。 

 

select * from 
(select * from product 
union 
select * from product2) as p1 
where product_id not in 
(select product_id from product where product_id not in (select product_id from product2) 
union
select product_id from product2 where product_id not in (select product_id from product));

3、每类商品中售价最高的商品都在哪些商店有售 。

select sp.shop_id,
       sp.shop_name,
       sp.quantity,
       p.product_id,
       p.product_name,
       p.product_type,
       mp.max_price
  from product as p
  inner join shop_product as sp on p.product_id= sp.product_id
  inner join(
select product_type, max(sale_price)  as max_price
  from product as p1
 group by product_type)  as mp on mp.product_type= p.product_type
   and p.sale_price= mp.max_price;

 

4、分别使用内连结和关联子查询每一类商品中售价最高的商品。

-- 内连接
select p.product_id,
       p.product_name,
       p.product_type,
       mp.max_price,
       p.regist_date
  from product as p
  inner join(
select product_type, max(sale_price)  as max_price
  from product
 group by product_type)  as mp on p.product_type= mp.product_type
   and p.sale_price= mp.max_price;

 

-- 关联子查询
select p1.product_id,
       p1.product_name,
       p1.product_type,
       p1.sale_price as max_price,
       p1.regist_date
  from product as p1
 where sale_price=(
select max(sale_price)  from product as p2
 where p1.product_type= p2.product_type
 group by product_type) ;

5、用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。