【SQL04】集合运算

原文来自datawhale组队学习-SQL编程语言，资料ref：SQL第四章：集合运算

表的加减法

UNION

练习题:

假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集.

retrieve 毛利率超过 50% 或 售价低于 800的商品

SELECT *
	FROM
   		product
	WHERE
     sale_price / purchase_price >= 1.5
UNION
SELECT *
   FROM
        product
   WHERE
       sale_price < 800;

不用UNION 因为我们是在一个表里找，所以可以用or完成。

SELECT *
FROM product
WHERE
   	sale_price < 800
OR
    sale_price / purchase_price >= 1.5;

分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品.

同样，我们还是在同一个表 product里做这个query。可以用UNION 或 OR 达成目的。

SELECT *
	FROM product
	WHERE sale_price / purchase_price < 1.3
UNION
SELECT *
	FROM product
	WHERE sale_price/purchase_price IS NULL;

SELECT *
FROM product
WHERE
   sale_price / purchase_price < 1.3
   OR
   sale_price/purchase_price IS NULL;

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

分别从product和product2这两个表中找出售价高于500的商品，使用UNION将结果合并展示

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

UNION ALL

练习

商店决定对product表中利润低于50%和售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集

SELECT *
	FROM product
	WHERE sale_price/purchase_price < 1.5
	UNION ALL
	SELECT *
	FROM product
	WHERE sale_price < 1000;

隐式类型转换

练习

使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性.

🤯
以前从来没注意诶… 学到了学到了

借助并集和差集迂回实现交集运算 INTERSECT

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

我想来想去，想去想来，这为啥要绕个圈用 「两个集合的交可以看作是两个集合的并去掉两个集合的对称差.」来做？这不是直接用where里IN就可以取出来了？

SELECT *
	FROM product
	WHERE product.product_id IN (
	    SELECT product2.product_id
	    FROM product2
	    );

连结 JOIN

INNER JOIN

练习

找出每个商店里的衣服类商品的名称及价格等信息

solution 1:

• 明确我们需要使用的表：shop_product 和 product
• 两个表通过共有的列 product_id 来连结
• 如果不申明是什么JOIN，默认是INNER JOIN
• JOIN 完我们就得到了一个有根据两表共有的那些product_id来连结的表，我们再从中找出满足 product_type为衣服的就好

SELECT
   shop_id, shop_name, product.product_id, product_name, product_type, purchase_price
FROM
	shop_product
JOIN
	product ON product.product_id = shop_product.product_id
WHERE
	product.product_type = "衣服";

slotuion 2:

• 因为product_type只存在在product表中，我们可以先过滤掉不满足 product_type=衣服的记录
• 再和上面的思路一样把这个过滤完的表和shop_product连结起来

SELECT
	shop_product.product_id, shop_name, a.product_id, product_name, product_type, purchase_price
FROM
	shop_product
INNER JOIN
	(SELECT 
		product_id, product_name, product_type, purchase_price
	 FROM 
		product
	 WHERE 
		product_type = "衣服") a
ON shop_product.product_id = a.product_id;

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

-- 不使用子查询
SELECT shop_id, shop_name, a.product_id, quantity, p.product_id, product_name, product_type, sale_price
FROM
	shop_product a
JOIN
	product p on a.product_id = p.product_id
WHERE
	shop_name = "东京"
	AND
	sale_price < 2000;

-- 使用子查询
SELECT a.*, b.*
FROM
	(SELECT *
	FROM shop_product
	WHERE shop_name = "东京") a
JOIN
	(SELECT product.*
	FROM product
	WHERE sale_price < 2000) b
ON a.product_id = b.product_id;

INNER JOIN + GROUP BY 练习

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

SELECT
	shop_id, shop_name, MAX(sale_price)
FROM
	shop_product
JOIN
	product p on shop_product.product_id = p.product_id
GROUP BY
	shop_id, shop_name;

上述查询得到了每个商品售价最高的商品, 但并不知道售价最高的商品是哪一个.如何获取每个商店里售价最高的商品的名称和售价?

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

思路
第一步

• 先从product表中找出来 每类商品中的最高售价

SELECT
	    p1.product_type, MAX(sale_price) as max_price
FROM
	    product p1
GROUP BY
	    p1.product_type

第二步

• 我们让product表和自己JOIN product表一，product表二
• JOIN ON子句里放商品种类，这样我们就把表二的max_price连结到表一里去了（这里用LEFT JOIN也是一样的）
• 我们使用WHERE子句去找出符合「售价等于max_price」的商品

SELECT
	    *
	FROM
	     product p2
	JOIN (
	    SELECT
	    p1.product_type, MAX(sale_price) as max_price
	FROM
	    product p1
	GROUP BY
	    p1.product_type
	         ) a
	ON p2.product_type = a.product_type
	WHERE
	    p2.sale_price = max_price;

第三步

• 因为我们在第二步已经找出来了那些售价等于该品类最高售价的商品，已经获得了他们的product_id
• 我们可以把这个第二步里获得的零时表连结到shop_product上，基于两表共有的列 product_id
• 这里还是用INNER JOIN，可以自动帮我们把不满足连结条件的、在shop_product里的记录排除掉

SELECT
   shop_id, shop_name,temp.product_id, product_name, sale_price
FROM
	    shop_product
JOIN
	( -- the temp table we created in step 2
		SELECT
	        product_id, product_name, sale_price
	    FROM
	         product p2
	    JOIN (
	        SELECT
	        p1.product_type, MAX(sale_price) as max_price
	    FROM
	        product p1
	    GROUP BY
	        p1.product_type
	             ) a
	    ON p2.product_type = a.product_type
	    WHERE
	        p2.sale_price = max_price) temp
ON shop_product.product_id = temp.product_id;

还是第三步
step 3 - 如果是单纯的只想知道有哪些商店有售，并不在乎售些什么

SELECT
   DISTINCT shop_id, shop_name
	FROM
	    shop_product
	WHERE
	    shop_product.product_id IN
	        (SELECT
	            product_id
	        FROM
	             product p2
	        JOIN (
	            SELECT
	            p1.product_type, MAX(sale_price) as max_price
	        FROM
	            product p1
	        GROUP BY
	            p1.product_type
	                 ) a
	        ON p2.product_type = a.product_type
	        WHERE
	            p2.sale_price = max_price);

OUTER JOIN

练习

SELECT    
	product.product_id, product_name,sale_price, shop_id, shop_name, quantity
FROM
	product
LEFT JOIN
	shop_product
	 ON product.product_id = shop_product.product_id
WHERE
	quantity < 50
	OR
	quantity IS NULL;

但其实我这个方法不是很好，因为做着题有看到底下说quantity为NULL的会取不出来。那如果是我自己直接做，可能也不会一开始就想到这个问题。所以嗯，教材给出的那种，先把quantity<50找出来再去JOIN product表比较好。

SELECT
	product.product_id, product_name, sale_price,
	shop_id, shop_name, quantity
FROM
	product
LEFT JOIN
	(SELECT 
		sp.product_id, shop_id, shop_name, quantity
		FROM shop_product sp
		WHERE quantity < 50) temp
ON product.product_id = temp.product_id;

ON 子句进阶 – 非等值连结

交叉连结—— CROSS JOIN(笛卡尔积)

SQL过时语法

SELECT..
FROM <table_1>, <table_2>
WHERE table_1.id = table_2.id

这居然属于过时语法 …

• 无法马上判断出到底是内连结还是外连结
• 无法立马判断出哪个是连结条件，哪个是限制条件
• 不知道这样的语法还能使用多久（每个DBMS的开发者都会考虑放弃过时的语法，转而支持新的语法）