【MySQL】mysql提升题

系列文章目录

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

• 系列文章目录
• 前言
• 一、pandas是什么？
• 二、使用步骤
  • 1.引入库
  • 2.读入数据
• 总结

---

前言

        整理之前学完的MySQL练习题，发现还有三道题目没有做，突然心血来潮试试自己MySQL有没有忘记。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一.现在有三张学生表，现在要补全学生信息

---

        创建三张表以及插入数据，每一张表的数据都是不完整的，现在需要你把他补全信息

补全结果如下：
s001,张三,23,男
s002,李四,23,男
s003,吴鹏,25,男
s004,琴沁,20,女
s005,王丽,20,女
s006,李波,21,男
s007,刘玉,21,男
s008,萧蓉,21,女
s009,陈萧晓,23,女
s010,陈美,22,女
s011,貂蝉,22,女
s012,吴磊,22,男

CREATE TABLE student1(  
  sno VARCHAR(4) PRIMARY KEY,
  sname VARCHAR(10),
  sage INT(2),
  ssex VARCHAR(1)
);

INSERT INTO student1 VALUES ('s001','张三',23,'男');
INSERT INTO student1 VALUES ('s002','null',23,'');
INSERT INTO student1 VALUES ('s003','',,'男');
INSERT INTO student1 VALUES ('s004','琴沁',20,'女');
INSERT INTO student1 VALUES ('s005','',20,'女');
INSERT INTO student1 VALUES ('s007','',21,'男');
INSERT INTO student1 VALUES ('s009','陈萧晓',23,'女');
INSERT INTO student1 VALUES ('s010','陈美',,'女');


CREATE TABLE student2(  
  sno VARCHAR(4) PRIMARY KEY,
  sname VARCHAR(10),
  sage INT(2),
  ssex VARCHAR(1)
);

INSERT INTO student2 VALUES ('s001','张三',23,'男');
INSERT INTO student2 VALUES ('s002','李四',,'男');
INSERT INTO student2 VALUES ('s003','',25,'男');
INSERT INTO student2 VALUES ('s006','李波',21,'男');
INSERT INTO student2 VALUES ('s007','刘玉',,'男');
INSERT INTO student2 VALUES ('s008','萧蓉',21,'女');
INSERT INTO student2 VALUES ('s010','',22,'女');
INSERT INTO student2 VALUES ('s011','貂蝉',22,'女');

CREATE TABLE student3(  
  sno VARCHAR(4) PRIMARY KEY,
  sname VARCHAR(10),
  sage INT(2),
  ssex VARCHAR(1)
);

INSERT INTO student3 VALUES ('s001','张三',23,'男');
INSERT INTO student3 VALUES ('s002','李四',null,'男');
INSERT INTO student3 VALUES ('s003','吴鹏',25,'');
INSERT INTO student3 VALUES ('s004','琴沁',20,'女');
INSERT INTO student3 VALUES ('s005','王丽',20,'女');
INSERT INTO student3 VALUES ('s009','陈萧晓',23,'女');
INSERT INTO student3 VALUES ('s010','陈美',,'女');
INSERT INTO student3 VALUES ('s012','吴磊',22,'男');

         首先我们需要先获取他们的学号sno，用UNION把三张表的sno结果合并，与UNION ALL区别在于UNION去重；得到sno后，可以使用case when 判断每个字段的内容是否为空值，如果不为空就b.name,一一张表的去判断，后面的字段也是如此（套娃）。

select a.sno,
case when b.sname <>'' then b.sname 
     when c.sname <>'' then c.sname 
     else d.sname end as sname,
case when b.sage is not null then b.sage 
		 when c.sage is not null then c.sage 
		 else d.sage end as sage,
case when b.ssex <>'' then b.ssex
     when c.ssex <>'' then c.ssex
		 else d.ssex end as ssex
from (SELECT sno from student1
UNION
SELECT sno from student2
UNION
SELECT sno from student3) a 
left join student1 b on a.sno=b.sno
left join student2 c on a.sno=c.sno
left join student3 d on a.sno=d.sno

二、一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，用一条sql 语句显示所有可能的比赛组合

# 创建表team
create table team (name varchar(20));
# 插入数据
insert into team values('a')
insert into team values('b')
insert into team values('c')
insert into team values('d')


select a.name,b.name From team a join team b on a.name<b.name

运行结果：
name name1
a    b
a    c
a    d
b    c
b    d
c    d

三、现在有张保单信息表,里面有以下数据,现在要统计客户(cust_id)的在保时长,即统计各类险种(risk_id)的在保时长,去除重复的在保时间;起保时间(start_date),终保时间(end_date)

        主要思路就是利用窗口函数lead把start_date日期向上移动一位，然后判断日期进行计算.

cust_id,risk_id,start_date,end_date
01,01,'2012-05-01','2013-08-25'
01,02,'2011-05-01','2015-10-01'
01,03,'2009-01-01','2012-07-30'
01,04,'2018-06-14','2020-01-01'
01,05,'2003-12-01','2004-05-30'

# 创建表t_policy_info
create table t_policy_info (cust_id int,
                            risk_id,int,
                            start_date date,
                            end_date date)
# 插入数据
insert into t_policy_info values(01,01,'2012-05-01','2013-08-25')
insert into t_policy_info values(01,02,'2011-05-01','2015-10-01')
insert into t_policy_info values(01,03,'2009-01-01','2012-07-30')
insert into t_policy_info values(01,04,'2018-06-14','2020-01-01')
insert into t_policy_info values(01,05,'2003-12-01','2004-05-30')

select *,
case when n>end_date then datediff(end_date,start_date)
		 when n<end_date then datediff(n,start_date)
		 when n is null then datediff(end_date,start_date) end 在保时长
from(
select *,lead(start_date,1)over(partition by cust_id order by start_date) n from t_policy_info
) a