cfh：数据分析师 Mysql练习题（附答案）

Mysql练习（附答案）

• 练习一 分组、过滤、连接
• 练习二 增改表字段、改数据、计算
• 练习三 IF条件过滤
• 练习四 纵向连接,日期函数,子查询
• 练习五 时间函数，子查询，多条件查询
• 知识扩展

练习一 分组、过滤、连接

知识点：分组group by、过滤where…having、连接left/right…join…on

现有投资表和业务员表两张表，表结构及数据如下：

#创建invest表create table invest(created_at date,user_id varchar(20),invest_item varchar(100),investamount decimal(38,10));#给invest插入数据insert into invest values("2017/11/1","A123","CFH",100000),("2017/12/5","A123","AX",450000),("2017/12/11","A123","CH",700000),("2017/12/6","B456","CFH",1500000),("2017/12/26","B456","AX",800000),("2018/1/6","C789","JUIN",300000);#创建agent表create table agent(user_id varchar(20),start_date date,end_date date,agent_id varchar(20));#给agent插入数据insert into agent values("A123","2016/1/1","2017/12/4",10001),("A123","2017/12/5","3001/12/31",10002),("B456","2015/10/31","2016/12/15",10001),("B456","2016/12/16","3001/12/31",10003),("C789","2015/1/1","3001/12/31",10002);

开始做题：
1、计算2017年每笔投资均大于50万的用户

select user_id from invest #从invest表中查询user_idwhere year(created_at)=2017 #条件为created_at=2017group by user_id #对user_id分组having min(investamount)>500000; #条件最小投资金额investamount小于50万

2、计算2017年仅投资过CFH和AX产品的用户

select user_id from investwhere year(created_at)=2017group by user_id having sum(invest_item="CFH")*sum(invest_item="AX") #同时满足两个条件and count(distinct invest_item)=2; #容易入坑的地方：投资项目要等于2

3、计算归属于10002业务员的投资金额

SELECT b.agent_id,SUM(investamount) 投资金额FROM invest a JOIN agent b on a.user_id=b.user_id #连接两表and a.created_at BETWEEN b.start_date and b.end_date #投资年份需要在业务员的工作时间范围内WHERE b.agent_id