Datawhale sql 课程 task-04 学习总结

UNION Vs OR

SELECT product_id,product_name, product_type, sale_price, purchase_price 
FROM product
WHERE sale_price - purchase_price > 0.5* purchase_price
UNION 
SELECT product_id,product_name, product_type, sale_price, purchase_price 
FROM product
WHERE purchase_price < 800; 


SELECT product_id,product_name, product_type, sale_price, purchase_price 
FROM product
WHERE sale_price - purchase_price > 0.5* purchase_price
OR purchase_price < 800; 

上面两段code, 分别一个用UNION, 另外一个用OR, 都能得到两个条件下的并集。

这里就有一个疑问了，那为什么用UNION，写起来还麻烦。课程里做了解释，上面的两段code都是在相同数据集进行并集操作，但是如果两个数据集不同就必须用OR了。

UNION ALL 可以不让UNION去除两个tables的重复值

Inner join

交集现在还不能用 INTERSECT, 现在只能用 INNER JOIN来求交集

SELECT p1.product_id, p1.product_name
FROM product AS p1
INNER JOIN product2 AS p2
ON p1.product_id = p2.product_id;

NOT IN

计算差集现在不能用EXCEPT, 只能用NOT IN来实现

Inner join and where 

分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.purchase_price
  FROM product AS P
  INNER JOIN shopproduct AS SP
  ON SP.product_id = P.product_id
  WHERE P.product_type = '衣服';

练习题

4.1

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

SELECT * 
FROM product AS P
WHERE sale_price > 500 
UNION 
SELECT * 
FROM product2 AS P
WHERE sale_price > 500;

4.2

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

SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

4.3

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

SELECT SP.shop_name
      ,P.product_name
      ,MAX(P.sale_price) AS max_price
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_name,P.product_name; 

4.4

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

- 关联子查询
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
   GROUP BY product_type); 

- 内连接
SELECT p1.product_type,
	   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;

4.5

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

暂时不会，后补