Task04:集合运算_崩撤卖溜

1

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

2

SELECT *
  FROM product
 WHERE product_id in
       (SELECT product_id
          FROM product2);

3

SELECT sail.product_id, shop_name, product_type
  FROM (
		SELECT product_id, max_price.product_type
		  FROM (SELECT *
		          FROM product
		         UNION
		        SELECT *
		          FROM product2) AS all_product
		  JOIN (SELECT MAX(sale_price) AS max, product_type
				  FROM (SELECT *
				          FROM product
				         UNION
				        SELECT *
				          FROM product2) AS all_product
				 GROUP BY product_type) AS max_price
		    ON all_product.product_type = max_price.product_type
		 WHERE sale_price = max
	   ) AS id
  LEFT JOIN ShopProduct AS sail
    ON id.product_id = sail.product_id;

4

#内连结
SELECT product_id
FROM (SELECT *
        FROM product
       UNION
      SELECT *
        FROM product2) AS all_product
  JOIN (SELECT MAX(sale_price) AS max, product_type
	      FROM (SELECT *
	              FROM product
	             UNION
	            SELECT *
	              FROM product2) AS all_product
	     GROUP BY product_type) max_price
    ON all_product.product_type = max_price.product_type
 WHERE sale_price = max;
# 关联子查询
SELECT product_id
  FROM (SELECT *
          FROM product
         UNION
        SELECT *
          FROM product2) AS p1
 WHERE sale_price = (SELECT MAX(sale_price)
		               FROM (SELECT *
		                     FROM product
		                    UNION
		                   SELECT *
		                     FROM product2) AS p2
                      WHERE p2.product_type = p1.product_type
		              GROUP BY product_type);

5

SELECT product_id, product_name, sale_price,
	(SELECT SUM(sale_price)
       FROM product AS p2
      WHERE p2.sale_price <= p1.sale_price) AS sum
  FROM product AS p1
 ORDER BY sale_price;
浙ICP备19012682号