学习sql时找到的一个sql练习网站https://learnsql.com/
用下来感觉不错，虽然是英文网站，但并不影响使用
这边记录一下练习过程中的错题
页面里内容很齐全，数据表提示答案都有。。。
网页图
本题共涉及到3个表，分别是purchase_item，purchase，customer
purchase_item：采购物品 采购编号，名字以及数量
purchase_item
purchase：采购 客户编号，年份
purchase
customer：客户 名字，国家
customer
看下题目要求
The owner of the shop would like to see each customer's
- id (name the column cus_id).
- name (name the column cus_name).
- id of their latest purchase (name the column latest_purchase_id).
- the total quantity of all flowers purchased by the customer, in all purchases, not just the last purchase (name the column all_items_purchased).
题目要求查询的结果包含4列 都要求重命名
- 客户id
- 客户名字
- 每个客户最新采购的id
- 每个客户采购的总量
首先第1，2个是可以直接得出的
select customer.id as cus_id,customer.name cus_name from customer
然后第3个，关系到客户还有采购ID，这边看到采购表中有客户ID以及采购的编号。最新的id就是对应最大的id
select max(purchase.id) from purchase where customer_id=customer.id
最后，第4个
一个客户id-->可以有多个采购id-->根据采购ID得到总数量
select sum(quantity) from purchase_item --总数量
where purchase_id in --根据采购id
(select purchase.id from purchase
where customer_id=customer.id) --根据客户id，将采购id归类
最后得到完整的sql语句
select customer.id as cus_id,customer.name as cus_name,
(select max(purchase.id) from purchase
where customer_id=customer.id) as latest_purchase_id,
(select sum(quantity) from purchase_item
where purchase_id
in (select purchase.id from purchase
where customer_id=customer.id)) as all_items_purchased
from customer
