SQL编程学习04-集合运算

练习题

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;

结果如下：