SQL CH4

---

4.1

SELECT * FROM product where sale_price>500
UNION
SELECT * FROM product2 where sale_price>500

4.2

SELECT * FROM product
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 * FROM (
 SELECT p.product_type, product_id 
 from product p 
inner join
 (SELECT product_type,max(sale_price) as sale_price FROM product GROUP BY product_type) t
 on p.product_type=t.product_type and p.sale_price=t.sale_price
 ) t
left join shopproduct s
on t.product_id=s.product_id

4.4

 SELECT * 
 from product p 
inner join
 (SELECT product_type,max(sale_price) as sale_price FROM product GROUP BY product_type) t
 on p.product_type=t.product_type and p.sale_price=t.sale_price

4.5 利用左自连接和on中使用谓词实现排序

SELECT product_id,product_name,sale_price,COUNT(y_id) as my_rank
FROM
 (
SELECT x.product_id,x.product_name,x.sale_price,y.product_id as y_id
FROM product x
left JOIN product y
ON ((x.sale_price > y.sale_price) or (x.sale_price = y.sale_price and x.product_id<=y.product_id))
 ) t
 GROUP BY product_id,product_name,sale_price
 ORDER BY my_rank

SELECT product_id,product_name,sale_price,COUNT(y_id) as my_rank,sum(y_price) as cum_price
FROM
 (
SELECT x.product_id,x.product_name,x.sale_price,y.product_id as y_id,y.sale_price as y_price
FROM product x
left JOIN product y
ON ((x.sale_price > y.sale_price) or (x.sale_price = y.sale_price and x.product_id<=y.product_id))
 ) t
 GROUP BY product_id,product_name,sale_price
 ORDER BY cum_price