基础语法回顾

1.SQL110 插入记录（一）

牛客后台会记录每个用户的试卷作答记录到exam_record表，现在有两个用户的作答记录详情如下：

• 用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
• 用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。

试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。

该题最后会通过执行SELECT uid, exam_id, start_time, submit_time, score FROM exam_record;来对比结果

题解：

1
2
3
4

insert into exam_record(uid,exam_id,start_time,submit_time,score) 
VALUES (1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12' ,90),
(1002,9002,'2021-09-04 07:01:02',null,NULL);

按照顺序插入即可，注意一一对应

2.

牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

示例数据：examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

示例数据：exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）iduidexam_idstart_timesubmit_time
score1100190012020-01-02 09:01:012020-01-02 09:21:01
8021001
9001
2021-05-02 10:01:012021-05-02 10:30:01
81310019001
2021-06-02 19:01:01
2021-06-02 19:31:01
84
410019002
2021-09-05 19:01:01
2021-09-05 19:40:0189
51001
90012021-09-02 12:01:01
(NULL)
(NULL)
61001
9002
2021-09-01 12:01:01
(NULL)
(NULL)
710029002
2021-02-02 19:01:01
2021-02-02 19:30:01
87810029001
2021-05-05 18:01:01
2021-05-05 18:59:02909
10039001
2021-09-07 12:01:01
2021-09-07 10:31:01
501010049001
2021-09-06 10:01:01
(NULL)
(NULL)

根据输入你的查询结果如下：

题解：

1
2
3
4
5
6
7

SELECT tag, difficulty,
    ROUND((SUM(score) - MAX(score) - MIN(score)) / (COUNT(score) - 2), 1) AS clip_avg_score
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE tag = 'SQL' AND difficulty = 'hard'
GROUP BY tag, difficulty;

根据sql查询的分别出现的列，然后来根据这些列来写

主要是

1

ROUND((SUM(score) - MAX(score) - MIN(score)) / (COUNT(score) - 2), 1) AS clip_avg_score

这个保留一位小数，计算平均数

然后使用联合查询

将examination_info和exam_record连接起来，其主键是exam_id

然后where条件

最后可以来个按组排序

2.SQL200 查找最晚入职员工的所有信息

有一个员工employees表简况如下:

请你查找employees里最晚入职员工的所有信息，以上例子输出如下:

1

select * from employees order by hire_date desc limit 1;

easy题目不必多说。

3.SQL201 查找入职员工时间升序排名的情况下的倒数第三的员工所有信息

有一个员工employees表简况如下:

请你查找employees里入职员工时间升序排名的情况下倒数第三的员工所有信息，以上例子输出如下:

注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个,存在多个员工的情况按照emp_no升序排列。

1
2
3
4
5
6

select * from employees where hire_date = (
    select DISTINCT hire_date
    from employees
    order by hire_date desc
    limit 1 offset 2
);

使用子查询，然后去掉倒数第一第二

4.SQL202 查找当前薪水详情以及部门编号dept_no

有一个全部员工的薪水表salaries简况如下:

有一个各个部门的领导表dept_manager简况如下:

请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:

1
2
3
4

select e.emp_no,e.salary,e.from_date,e.to_date,d.dept_no
from salaries as e,dept_manager as d
where e.emp_no = d.emp_no
order by e.emp_no;

easy的多表查询

5.SQL42 分析客户逾期情况(middle)

有贷款信息表：loan_tb（agreement_id：合同id，customer_id：客户id，loan_amount：贷款金额，pay_amount：已还金额，overdue_days：逾期天数）

客户信息表：customer_tb（customer_id：客户id，customer_age：客户年龄，pay_ability：还款能力级别）

请根据以上数据分析各还款能力级别的客户逾期情况，按照还款能力级别统计有逾期行为客户占比。要求输出还款能力级别、逾期客户占比。

注：逾期客户占比要求按照百分数形式输出并四舍五入保留 1 位小数，最终结果按照占比降序排序。

示例数据结果如下：

结果解释：

还款能力级别为 C 的客户有1113、1116、1119，其中有逾期行为的客户为 1113、1119，故结果为 2/3=66.7%；其他结果同理。

我去了，对现在的我还是有些难的啊这个题目。

1
2
3
4
5
6
7
8

select c.pay_ability,
    concat(format(cast(sum(case when l.overdue_days is not null then 1 else 0 end)as real)*100.0/count(DISTINCT c.customer_id),1),'%')

as overdue_ratio
from customer_tb as c
left join loan_tb as l on c.customer_id=l.customer_id
group by c.pay_ability
order by overdue_ratio desc;

最主要的就是这个

1

concat(format(cast(sum(case when l.overdue_days is not null then 1 else 0 end)as real)*100.0/count(DISTINCT c.customer_id),1),'%')

计算的过程，主要是浮点数的格式话的问题，其他的还好其实。

6.SQL204 查找所有员工的last_name和first_name以及对应部门编号dept_no

有一个员工表，employees简况如下:

有一个部门表，dept_emp简况如下:

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下:

1
2
3

select e.last_name ,e.first_name ,d.dept_no
from employees as e
left join dept_emp as d on e.emp_no = d.emp_no;

7.SQL46 查询培训指定课程的员工信息

某公司员工信息数据及员工培训信息数据如下：

员工信息表staff_tb(staff_id-员工id，staff_name-员工姓名，staff_gender-员工性别，post-员工岗位类别，department-员工所在部门)，如下所示：

员工培训信息表cultivate_tb(info_id-信息id，staff_id-员工id，course-培训课程)，如下所示：

注：该公司共开设了三门课程，员工可自愿原则性培训0-3项；

问题：请查询培训课程course3的员工信息？

注：只要培训的课程中包含course3课程就计入结果

要求输出：员工id、姓名，按照员工id升序排序；
示例数据结果如下：

1
2
3
4

select s.staff_id,s.staff_name
from staff_tb as s
left join cultivate_tb as c on s.staff_id=c.staff_id
where concat(',',c.course,',') like '%course3%';

使用分隔符来进行模糊匹配，确保只匹配到course3

8.SQL43 获取指定客户每月的消费额

某金融公司某项目下有如下 2 张表：

交易表 trade（t_id：交易流水号，t_time：交易时间，t_cus：交易客户，t_type：交易类型【1表示消费，0表示转账】，t_amount：交易金额）:

客户表 customer（c_id：客户号，c_name：客户名称）:

现需要查询 Tom 这个客户在 2023 年每月的消费金额（按月份正序显示），示例如下：

请编写 SQL 语句实现上述需求。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select
    DATE_FORMAT(t.t_time, '%Y-%m') AS time,
    SUM(t.t_amount) AS total
from
    trade as t
    left join customer as c on t.t_cus = c.c_id
where
    c.c_name = 'Tom'
    AND t.t_type = 1
    AND YEAR(t.t_time) = 2023
GROUP BY
    DATE_FORMAT(t.t_time, '%Y-%m')
ORDER BY
    time;

9.查询连续入住多晚的客户信息？

某酒店客房信息数据及某晚入住信息数据如下：

客房信息表guestroom_tb(room_id-房间号,room_type-房间类型,room_price-房间价格)，如下所示：

入住信息表checkin_tb(info_id-信息id.room_id-房间号,user_id-客户id,checkin_time-入住时间,checkout_time-退房时间)，

该表存储该晚客户入住信息及后续退房信息，如下所示：

问题：请查询该酒店从6月12日开始连续入住多晚的客户信息？要求输出：客户id、房间号、房间类型、连续入住天数（按照连续入住天数的升序排序，再按照房间号的升序排序，再按照客户id的降序排序）示例数据结果如下：user_idroom_idroom_typedays2031003商务单人房22051007商务标准房32081009商务标准房4解释：以客户203为例，在2022-06-12入住酒店，在2022-06-14退房，连续在12日晚、13日晚入住在该酒店，故结果如上；其他结果同理。

1
2
3
4
5
6
7

select c.user_id,c.room_id,g.room_type,
datediff(c.checkout_time, c.checkin_time) as days
from guestroom_tb as g
join checkin_tb as c on g.room_id = c.room_id
where date(c.checkin_time) ='2022-06-12' 
and datediff(c.checkout_time, c.checkin_time)>=2
order by days ASC,c.room_id,c.user_id desc;

这个题目主要是时间函数的使用

10.SQL45 统计所有课程参加培训人次

某公司员工培训信息数据如下：

员工培训信息表cultivate_tb(info_id-信息id,staff_id-员工id,course-培训课程)，如下所示：

注：该公司共开设了三门课程，员工可自愿原则性培训0-3项，每项课程每人可培训1次。

问题：请统计该公司所有课程参加培训人次？

示例数据结果如下：

解释：course1课程共有员工1、3、4、7共4名员工培训；

course2课程共有员工1、2、4、7共4名员工培训；

course3课程共有员工3、4、5共3名员工培训。

1
2
3
4
5
6

SELECT 
    SUM(CONCAT(',', c.course, ',') LIKE '%,course1,%') +
    SUM(CONCAT(',', c.course, ',') LIKE '%,course2,%') +
    SUM(CONCAT(',', c.course, ',') LIKE '%,course3,%') AS staff_nums
FROM 
    cultivate_tb c;

更具性价比的方法