4.1找出 product 和 product2 中售价高于 500 的商品的基本信息。
4.2借助对称差的实现方式, 求product和product2的交集。
SELECT *
FROM product
WHERE product_id IN (SELECT product_id FROM product2)
UNION
SELECT *
FROM product2
WHERE product_id IN (SELECT product_id FROM product);
4.3每类商品中售价最高的商品都在哪些商店有售 ?
SELECT P.product_id,
P.product_name,
P.product_type,
MAX(P.sale_price) AS max_price,
sp.shop_name
FROM product AS P
INNER JOIN shop_product as sp
ON P.product_id=SP.product_id
GROUP BY P.product_type;
4.4分别使用内连结和关联子查询每一类商品中售价最高的商品。
SELECT product_type,
MAX(sale_price) AS MAX_price
FROM product
GROUP BY product_type;
4.5用关联子查询实现:在product
表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT product_id
,product_name
,sale_price
,SUM(P2_price) AS SUM_sale_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
OR P1.sale_price=P2.sale_price
AND P1.product_id<=P2.product_id))
ORDER BY P1.product_id,P1.sale_price
) AS X
GROUP BY product_id,product_name,sale_price
ORDER BY sale_price,SUM_sale_price;