根據下面的例表編寫 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減少的銷量，說明該品牌市場占有率下降，需要盡快分析原因，做出應對措施。