Task04_DW集合运算

4.1 交并补差（集合运算）

1.练习-UNION求并集

--1.毛利率超过 50%或者售价低于 800 的货物的存货量
SELECT *
FROM product
WHERE (sale_price-purchase_price)/purchase_price>0.5
UNION
SELECT *
FROM product
WHERE sale_price<800;

--2.用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品
SELECT *
FROM product
WHERE (sale_price-purchase_price)/purchase_price<0.3
UNION
SELECT *
FROM product
WHERE (sale_price-purchase_price)/purchase_price is null;

UNION ALL 不去重求并集

2.求对称差

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

UNION&OR, INTERSECT&AND, EXCEPT&NOT IN

4.2 连结 JOIN

使用某种关联条件(一般是判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算

4.2.1 内连结 INNER JOIN

FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

INNER 关键词表示使用了内连结

1.结合 WHERE 子句使用内连结, 需要把 WHERE 子句写在 ON 子句的后边;

也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来

--找出每个商店里的衣服类商品的名称及价格等信息
SELECT SP.shop_id,SP.shop_name,P.product_id,P.product_name,P.product_type
FROM product AS P
INNER JOIN
shopproduct AS SP
ON SP.product_id=P.product_id
WHERE P.product_type='衣服';

2.结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待

3.自连结，可以代替关联子查询

4.自然连结, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件； 使用自然连结还可以求出两张表或子查询的公共部分

SELECT *  FROM shopproduct NATURAL JOIN Product

5.利用内连结求交集

4.2.2 外连结 OUTER JOIN

-- 左连结     
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)>

外连结会根据外连结的种类有选择地保留无法匹配到的行。按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。

4.2.2.1 左连结与右连结

结合 WHERE 子句使用左连结, 把WHERE子句挪到外连结之前进行

4.2.2.2 全外连结

4.2.3 多表连结

4.2.4 非等值连结

SELECT product_id,product_name,sale_price,COUNT(P2_id) AS myrank
FROM
(SELECT P1.product_id,P1.product_name,P1.sale_price
,P2.product_id AS P2_id,P2.product_name AS P2_name,P2.sale_price AS P2_price 
FROM product AS P1
LEFT JOIN product AS P2
ON P1.sale_price<=P2.sale_price) AS X
GROUP BY product_id
ORDER BY myrank;
--根据售价进行排序

4.2.5 交叉连结 CROSS JOIN

4.3 练习题

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 allproduct
WHERE allproduct.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,P.product_id,product_name,P.product_type,P.MAX_PRICE
FROM shopproduct AS SP
INNER JOIN
(SELECT product_id,product_name,P1.product_type,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 sale_price=MAX_PRICE) AS P

①内连结查询每一类商品中售价最高的商品

SELECT product_id,product_name,P1.product_type,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 sale_price=MAX_PRICE;

②关联子查询每一类商品中售价最高的商品

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

5.不会呀

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

4.4 有一个问题需要请教

SELECT product_id,product_name,sale_price,COUNT(product_name)
FROM product
GROUP BY product_id;

执行结果：

SELECT product_id,product_name,sale_price,COUNT(product_name)
FROM product
GROUP BY product_type;

执行报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘shop.product.product_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

表示SELECT出现了非聚合列。

DW-SQL学习