SQL编程学习04-集合运算

最新推荐文章于 2022-05-29 11:00:20 发布

原创最新推荐文章于 2022-05-29 11:00:20 发布 · 296 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍了SQL查询在产品和商店数据中的应用，包括获取售价高于500的商品信息、求解产品交集、查找各类商品最高售价商品的商店分布以及使用关联子查询对商品售价进行排序和累计求和。通过实例展示了SQL在数据处理中的灵活性和实用性。

练习题

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

SELECT * FROM product;
SELECT * FROM product2;

SELECT * 
  FROM product AS p1 
 WHERE p1.sale_price > 500
 UNION # ALL 9 records
SELECT * 
  FROM product2 AS p2 
 WHERE p2.sale_price > 500;

结果如下：
在这里插入图片描述

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

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

SELECT * 
FROM (# a+b 
	 SELECT * -- product_id, product_name, product_type, sale_price, purchase_price, regist_data
	   FROM product 
	  UNION
	 SELECT *
	   FROM product2) AS plus
WHERE plus.product_id NOT IN(
	 # a-b union b-a 
	 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)
);

结果如下：
在这里插入图片描述

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

SELECT *
  FROM (SELECT p1.*
		  FROM product AS p1
		 WHERE p1.sale_price = (SELECT max(p2.sale_price)
			  					  FROM product AS p2
							     WHERE p1.product_type = p2.product_type)
								) AS max_p
  LEFT OUTER JOIN shop_product AS sp 
    ON sp.product_id = max_p.product_id;

4.4 内连结和关联子查询每一类商品中售价最高的商品

-- 1 关联子查询
SELECT p1.*
  FROM product AS p1
 WHERE p1.sale_price = (SELECT max(p2.sale_price)
						  FROM product AS p2
						 WHERE p1.product_type = p2.product_type);

-- 内连结
SELECT p1.*
  FROM product AS p1  
 INNER JOIN(-- 查询每类商品的最高价格
			 SELECT product_type, max(sale_price) AS m_price
			   FROM product
			  GROUP BY product_type) AS p2 
    ON p1.product_type = p2.product_type
 WHERE p1.sale_price = p2.m_price;

结果如下：
在这里插入图片描述

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

SELECT product_id
	   ,product_name
	   ,sale_price
	   ,SUM(p2_price) AS sum_price
 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 OUTER JOIN product AS p2 
		   ON p1.sale_price >= p2.sale_price
		ORDER BY p1.sale_price, p1.product_id
	 ) AS x 
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price, product_id;