TASK4

练习题1

找出 product 和 product2 中售价高于 500 的商品的基本信息。

解答：

select * from product where sale_price > 500
union
select * from product2 where sale_price > 500;

运行结果：

练习题2

借助对称差的实现方式, 求product和product2的交集。

解答：
由于 mysql 中只能使用 union， 因此根据题意构造
set (product - product2) U set (product2 - product) 来得到对称差：

select * from product where product_id not in (select product_id from product2)
union
select * from product2 where product_id not in (select product_id from product);

再根据 set (product) U set (product2) - set ( 对称差 ) 来得到交集：

select * from (select * from product union select * from product2) as u
	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)
							);

运行结果：

此结果与子查询直接求交集的结果相同

select * from product where product_id in (select product_id from product2);

练习题3

每类商品中售价最高的商品都在哪些商店有售 ？

解答：
可以先将 product 表与 shoppproduct 表连以 product_id 相同为条件作内连结：

select sp.shop_id, sp.shop_name, sp.quantity,
        p.product_id, p.product_name, p.product_type, p.sale_price
from product as p 
inner join shopproduct as sp 
on sp.product_id = p.product_id;

再将 product 表中每种类别的最大价格筛选出来：

select product_type, max(sale_price) as maxp from product 
            group by product_type

最后再根据类别和价格相等作为条件连结到下面：

select sp.shop_id, sp.shop_name, sp.quantity,
        p.product_id, p.product_name, p.product_type, p.sale_price,
        mp.maxp as '该类商品的最大售价' 
from product as p 
inner join shopproduct as sp 
on sp.product_id = p.product_id
inner join (
			select product_type, max(sale_price) as maxp from product 
            group by product_type
            ) as mp
on mp.product_type = p.product_type and p.sale_price = mp.maxp;

运行结果：

练习题4

分别使用内连结和关联子查询每一类商品中售价最高的商品。

解答：

内连结方式与上题相同：

select p.product_id, p.product_name, p.product_type, p.sale_price
from product as p 
inner join (
			select product_type, max(sale_price) as maxp from product 
            group by product_type
            ) as mp
on mp.product_type = p.product_type and p.sale_price = mp.maxp;

运行结果：

关联子查询方式与教程中的类似：

select p.product_id, p.product_name, p.product_type, p.sale_price
from product as p 
where sale_price = (
					select max(sale_price) from product as p1
                    where p.product_type = p1.product_type
					group by product_type
                    );

运行结果：

练习题5

用关联子查询实现：在product表中，取出 product_id, product_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

解答：

关联子查询整体结构同上题

select p.product_id, p.product_name, p.product_type, p.sale_price
from product as p 
where sale_price = (
					select max(sale_price) from product as p1
                    where p.product_type = p1.product_type
					group by product_type
                    );

本题中需要关联的条件是 sale_price，因此可以更改上面的关联子查询条件：

p.product_type = p1.product_type ---更改为---> p.sale_price >= p1.sale_price

同样的，由于教程中已经提及过的问题，即存在两个商品的 sale_price 相同，上述语句的结果可能会不准确，这一点后续再讨论。

下一步，由于不能用连结方式扩充列，因此需要将关联子查询放入 select 子句中以增加“累计求和“列：

select p.product_id, p.product_name, p.product_type, p.sale_price,
	(select sum(sale_price) from product as p1
	where p.sale_price >= p1.sale_price
	) as '累计求和'
from product as p 
order by sale_price;

此时可以得到如下的结果：

要解决那两个重复值的问题，按照关联子查询的逻辑，

当 p.sale_price 中传递给子查询 “0008 号的 100（sale_price）” 时，在 p1 中满足 p.sale_price >= p1.sale_price 条件的只有 0008 号，因此 sum(sale_price) 只会计算 0008 号的 sale_price 一个值；

当 p.sale_price 中传递给子查询 “0002 号的 500（sale_price）” 时，在 p1 中满足 p.sale_price >= p1.sale_price 条件的有 0008 号、0002 号、0006 号，因此 sum(sale_price) 只会计算 0008、0002、0006 号的三个 sale_price 值；传递 0006 号时同理；

需要解决的问题是，当 sale_price 相同但 product_id 不同时的统计先后问题，与教程中类似，可以增加一个关于 product_id 的条件

p.sale_price >= p1.sale_price ---更改为--->
p.sale_price > p1.sale_price
	or (p.sale_price = p1.sale_price and p.product_id >= p1.product_id)

此时当 p.sale_price 中传递给子查询 “0002 号的 500（sale_price）” 时，在 p1 中
满足 p.sale_price > p1.sale_price 条件的有 0008 号；
满足 p.sale_price = p1.sale_price 条件的有 0002 号、0006 号；
满足 p.product_id >= p1.product_id 条件的只有 0002 号，
因此 sum(sale_price) 只会计算 0008、0002 号的两个 sale_price 值；

同理传递 0006 号时，由于满足 p.product_id >= p1.product_id 条件的有 0002 号、0006 号 (0006>=0006, 0006>=0002) 因此会计算 0008、0002、0006 号三个 sale_price 值，即可以得到正确结果。

最终查询语句如下：

select p.product_id, p.product_name, p.product_type, p.sale_price,
	(select sum(sale_price) from product as p1
	where p.sale_price > p1.sale_price
	or (p.sale_price = p1.sale_price and p.product_id >= p1.product_id)
	) as '累计求和'
from product as p 
order by sale_price;

运行结果：