参考答案:task04

参考答案:

4.1

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

4.2

-- 两个集合的交集就是两个集合的并集减去两个集合的对称差
-- 先求并集
SELECT * FROM
(SELECT * FROM product
 UNION
 SELECT * FROM product2) T
-- 减去对称差
WHERE 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));

4.3

-- 取出想要的字段
SELECT sp.shop_id, sp.shop_name, sp.product_id ,p.product_type
  FROM shopproduct sp
  JOIN product p
    ON sp.product_id=p.product_id
 WHERE sp.product_id in
-- 过滤每个类型售价最高的商品
(SELECT product_id 
   FROM product p1
   JOIN (SELECT product_type,
                MAX(sale_price) as max_price 
           FROM product 
       GROUP BY product_type) p2 
     ON p1.product_type=p2.product_type AND p1.sale_price=p2.max_price);

4.4


 --方法1:关联子查询
 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

--方法2:先连接,再过滤
SELECT  P1.product_id,P1.product_name,P1.product_type,P1.sale_price,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
 WHERE P1.sale_price= p2.max_price; 

4.5


SELECT product_id, product_name, sale_price,
	   (SELECT SUM(sale_price) FROM product AS P2
	   -- ①价格更低的 ②价格相等,product_id不大于的(不包括下一行)
		WHERE ((P2.sale_price < P1.sale_price) OR (P2.sale_price = P1.sale_price AND P2.product_id<=P1.product_id))) AS cum_price
FROM product AS P1 
ORDER BY sale_price,product_id;