进阶Ⅲ-集合运算

学习资料：http://datawhale.club/t/topic/473/5 

笔记

1.1表的加法-UNION 去重

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

1.2union all 保留重复行

-- 保留重复行
SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;

2.1 join

2.1.1 inner join

第一种写法：用join on

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id;

第二种写法：用using

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
using(product_id)

2.1.2 WHERE 子句使用内连结

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

where 写在join on 的后面

2.1.3 WHERE 子句使用内连结

每个商店中, 售价最高的商品的售价分别是多少?

SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

2.1.4内连结与关联子查询

---第一种写法
SELECT product_type, product_name, sale_price
  FROM product AS P1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM product AS P2
                      WHERE P1.product_type = P2.product_type
                      GROUP BY product_type);
---第二种写法
SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.avg_price
  FROM product AS P1
 INNER JOIN 
   (SELECT product_type,AVG(sale_price) AS avg_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price > P2.avg_price;

2.1.5 求表的公共部份

SELECT * 
  FROM (SELECT product_id, product_name
          FROM product ) AS A 
NATURAL JOIN 
   (SELECT product_id, product_name 
      FROM product2) AS B;

2.1.6使用连结求交集

SELECT P1.*
  FROM product AS P1
 INNER JOIN product2 AS P2
    ON P1.product_id = P2.product_id

2.1.7外连接

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

练习题

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

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

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

SELECT * FROM
(SELECT * FROM product
 UNION
 SELECT * FROM product2) T
-- 减去对称差
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.product_id ,p.product_type
  FROM shopproduct sp
  JOIN product p
    ON sp.product_id=p.product_id
 WHERE sp.product_id in
-- 过滤每个类型售价最高的商品
(SELECT product_id 
   FROM product p1
   JOIN (SELECT product_type,
                MAX(sale_price) as max_price 
           FROM product 
       GROUP BY product_type) p2 
     ON p1.product_type=p2.product_type AND p1.sale_price=p2.max_price);

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

--方法1：关联子查询
 SELECT product_type, product_name, sale_price
  FROM product AS P1
 WHERE sale_price = (SELECT max(sale_price)
                       FROM product AS P2
                      WHERE P1.product_type = P2.product_type

--方法2：先连接，再过滤
SELECT  P1.product_id,P1.product_name,P1.product_type,P1.sale_price,P2.max_price
  FROM product AS P1
 INNER JOIN 
   (SELECT product_type,max(sale_price) AS max_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price= p2.max_price; 

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

SELECT product_id, product_name, sale_price,
	   (SELECT SUM(sale_price) FROM product AS P2
	   -- ①价格更低的 ②价格相等，product_id不大于的（不包括下一行）
		WHERE ((P2.sale_price < P1.sale_price) OR (P2.sale_price = P1.sale_price AND P2.product_id<=P1.product_id))) AS cum_price
FROM product AS P1 
ORDER BY sale_price,product_id;