作业4.1
select *
from product
where sale_price > 500
union
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 t2.shop_id,t1.product_id,t1.product_type,t1.sale_price
from(
select t2.product_id,t1.product_type,t2.sale_price
from(
select product_type,max(sale_price) as max_price_type
from(
select *
from product
union
select *
from product2)t
group by product_type)t1
left join(select *
from product
union
select *
from product2)t2
on t1.product_type = t2.product_type
and t1.max_price_type = t2.sale_price)t1
left join shopproduct t2
on t1.product_id = t2.product_id
-- 一个嵌套了好多好多层的sql~~
作业4.4
-- 内连接
select b.product_id
,a.product_type
,b.sale_price
from(
select product_type,max(sale_price) as max_price_type
from(
select *
from product
union
select *
from product2
)t1
group by product_type
)a
inner join(select *
from product
union
select *
from product2
)b
on a.product_type = b.product_type
and a.max_price_type = b.sale_price
-- 关联子查询
SELECT product_type, product_id, sale_price
FROM (
select *
from product
union
select *
from product2
) P1
WHERE sale_price = (SELECT max(sale_price)
FROM (
select *
from product
union
select *
from product2
)p2
WHERE P1.product_type = P2.product_type
GROUP BY product_type);
作业4.5
TAT想不出来。。明天继续想