根据下面的例表编写 SQL 查询语句
表 名 ： [Product]
表名： [Order]
表名：[Store]
考题：
1. 请查找符合下列要求的产品，并按照产品价格降序排列：Category 为 CategoryA 且颜色为 yellow，或者 Weight 大于 5。
2. 请计算每一位客人的总购买金额(Amount)，总购买订单数，总购买产品件数(Quantity)，同一个客人同一天的订单算做一单，并筛选出总购买金额大于等于 800 的客人，按金额
降序排列。
3. 请给出每个城市(City)的总店铺数，总购买人数和总购买金额(Amount)，包含无购买记录的城市。
4. 请查找购买过 CategoryA 产品的客人，并计算每一位客人的平均订单金额(Amount)，一个订单编号(OrderID)算做一单。
5. 请查找每个城市(City)购买金额排名第二的客人，列出其购买城市，姓名，和购买金额。
6. 购买过ProductA且购买过ProductB的顾客人数。
7. 【SBA】某品牌产品 A 的销量在 2018 年第一季度 Q1 下降了 40%，相比于上一个季度。2018 年第一季度，该品牌有一款新产品 B 上市，无其他市场推广。请设计数据报表，来分析产品 A 销量下降的原因。
答案：
#1.请查找符合下列要求的产品，并按照产品价格降序排列：Category 为 CategoryA 且颜色为 yellow，或者 Weight 大于 5。
select *
from product
where category='categoryA' and color='yellow'
or weight>5
order by price desc;
#2.请计算每一位客人的总购买金额(Amount)，总购买订单数，总购买产品件数(Quantity)，同一个客人同一天的订单算做一单，并筛选出总购买金额大于等于 800 的客人，按金额降序排列。
分析思路：
[ordertable表]
#总购买金额=sum(amount)
#总订单数：按照客人name分组，统计distict orderdate
#总购买产品件数=按照名字分组，sum(quantity)
#分组之后的条件：总购买金额大于等于 800 ：sum(amount)>=800
#降序排列：order by sum(amount) desc
select name,sum(amount) as 总购买金额,count(distinct orderdate) as 总购买订单数,sum(quantity) as 总购买产品件数
from ordertable
group by name
having sum(amount)>=800
order by sum(amount) desc;
#3.请给出每个城市(City)的总店铺数，总购买人数和总购买金额(Amount)，包含无购买记录的城市。
分析思路：
#每个城市：按照city分组统计,
#总店铺数=count(Store)
#总购买人数=count(distict Name)
#总购买金额=sum(Amount)
#包括无购买记录的城市，Order和Store右联结，on a.store=b.store
select t1.city,count(distinct t1.store)as 总店铺数,count(distinct t1.`name`) as 总购买人数,sum(amount) as 总购买金额
from
(select
a.store,a.city,b.`name`,b.amount
from store a left join ordertable b
on a.store = b.store) t1
group by t1.city
#注意，子查询中不能使用select *，因为两表连接之后有两个store列，需要指定字段
4. 请查找购买过 CategoryA 产品的客人，并计算每一位客人的平均订单金额(Amount)， 一个订单编号(OrderID)算做一单。
分析思路：
#[product]category='categoryA'
#先根据categoryA找到购买productA，然后在此基础上对ordertable进行查询，条件product in (),按照name分组。
#平均金额=总金额/单数，=sum(amount)/count(orderid)
select name,sum(amount)/count(orderid) as 平均订单金额
from ordertable
where product in (select product from product where category='categoryA')
group by name
#5.请查找每个城市(City)购买金额排名第二的客人，列出其购买城市，姓名，和购买金额。
#每个城市：需要按照city分组，【store】
#购买金额=sum(amount)【ordertable】
#排名第二,order by 购买金额 desc,limit 1
#购买城市【store】,姓名【ordertable】，购买金额【ordertable】,右联结
select t1.city,t1.`name`,t1.amount_all,t1.rank
from
(select a.city,b.`name`,sum(b.amount) as 'amount_all',
Row_number() over(partition by a.city order by sum(b.amount) desc) as 'rank'
from
store a left join ordertable b on a.store = b.store
group by a.city,b.`name`) t1
where t1.rank=2
6.购买过ProductA且购买过ProductB的顾客人数。
#思路一：筛选出购买productA的客户名字，然后筛选出购买productB的客户名字，用内联结条件为a.name=b.name
select count(a.name)
from (select name from ordertable where product='ProductA') a
inner join
(select name from ordertable where product='ProductB') B
on a.name=b.name
or product='ProductB')
#思路二：先找到购买productB的客户名字，然后利用两个条件：product='productA' 和name=购买productB的客户名字
select count(name)
from ordertable
where product='productA' and name in (select name from ordertable where product='productB')
7.【SBA】某品牌产品A的销量在2018年第一季度Q1下降了40%，相比于上一个季度。2018年第一季度，该品牌有一款新产品B上市，无其他市场推广。请设计数据报表，来分析产品A销量下降的原因。
解题思路：数据报表应包含，产品类型、销售日期、销售数量，据此可以查看该品牌产品B上市是否对产品A销量造成影响：
- 如果产品B的销量大于等于产品A减少的销量，说明产品A销量下降的原因是产品B的上市；
- 如果产品B的销量小于产品A减少的销量，说明该品牌市场占有率下降，需要尽快分析原因，做出应对措施。