超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第二部分14-26）

最新推荐文章于 2022-04-14 15:14:46 发布

龍浮影

最新推荐文章于 2022-04-14 15:14:46 发布

阅读量191

点赞数

文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_48784015/article/details/108421588

版权

14. 至少有5名直接下属的经理

需求：Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。

展示效果：

Name
John

Create table If Not Exists 14_Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);
Truncate table 14_Employee;
insert into 14_Employee (Id, Name, Department, ManagerId) values (101, 'John', 'A', null);
insert into 14_Employee (Id, Name, Department, ManagerId) values (102, 'Dan', 'A', 101);
insert into 14_Employee (Id, Name, Department, ManagerId) values (103, 'James', 'A', 101);
insert into 14_Employee (Id, Name, Department, ManagerId) values (104, 'Amy', 'A', 101);
insert into 14_Employee (Id, Name, Department, ManagerId) values (105, 'Anne', 'A', 101);
insert into 14_Employee (Id, Name, Department, ManagerId) values (106, 'Ron', 'B', 101);

最终SQL:

-- 方法一：
SELECT
    Name
FROM
    14_Employee AS t1 
JOIN 
   (SELECT
        ManagerId
    FROM
        14_Employee
    GROUP BY 
        ManagerId
    HAVING
        COUNT(ManagerId) >= 5
    ) AS t2
ON  
    t1.Id = t2.ManagerId;

-- 方法二：
select
	Name
from
	14_Employee
where Id in (
			select
    			  ManagerId
			from
    			  14_Employee
			group by
    			  ManagerId
			having
    			  count(*)>=5 );

15. 给定数字的频率查询中位数

需求：请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

根据下表数据可以看出，原始数据为：0,0,1,2,2,2,3 中位数为2。

展示效果：

median
2.0000

Create table If Not Exists 15_Numbers (Number int, Frequency int);
Truncate table 15_Numbers;
insert into 15_Numbers (Number, Frequency) values (0, 2);
insert into 15_Numbers (Number, Frequency) values (1, 1);
insert into 15_Numbers (Number, Frequency) values (2, 3);
insert into 15_Numbers (Number, Frequency) values (3, 1);

提示：如果 n1.Number 为中位数，n1.Number（包含本身）前累计的数字应大于等于总数/2 ，同时n1.Number（不包含本身）前累计数字应小于等于总数/2。

最终SQL:

select
      avg(t.number) as median
from
      (select
             n1.number,
             n1.frequency,
             (select 
                   sum(frequency) 
              from 
                   15_Numbers n2
              where 
                   n2.number<=n1.number
             ) as asc_frequency,
             (select
                   sum(frequency)
              from 
                   15_Numbers n3 
              where 
                   n3.number>=n1.number
             ) as desc_frequency
      from 
             15_Numbers n1
      ) t
where 
      t.asc_frequency>= (select sum(frequency) from 15_Numbers)/2
      and t.desc_frequency>= (select sum(frequency) from 15_Numbers)/2;

16. 当选者

需求：请编写 sql 语句来找到当选者（CandidateId）的名字，

展示效果：

Name
B

Create table If Not Exists 16_Candidate (id int, Name varchar(255));
Create table If Not Exists 16_Vote (id int, CandidateId int);
Truncate table 16_Candidate;
insert into 16_Candidate (id, Name) values (1, 'A');
insert into 16_Candidate (id, Name) values (2, 'B');
insert into 16_Candidate (id, Name) values (3, 'C');
insert into 16_Candidate (id, Name) values (4, 'D');
insert into 16_Candidate (id, Name) values (5, 'E');
Truncate table 16_Vote;
insert into 16_Vote (id, CandidateId) values (1, 2);
insert into 16_Vote (id, CandidateId) values (2, 4);
insert into 16_Vote (id, CandidateId) values (3, 3);
insert into 16_Vote (id, CandidateId) values (4, 2);
insert into 16_Vote (id, CandidateId) values (5, 5);

最终SQL:

SELECT
    name AS 'Name'
FROM
    16_Candidate a
JOIN
    (SELECT
        CandidateId
    FROM
        16_Vote
    GROUP BY 
        CandidateId
    ORDER BY 
        COUNT(*) DESC
    LIMIT 1
    ) AS winner
WHERE
    a.id = winner.CandidateId;

17. 员工奖金

需求：选出所有 bonus < 1000 的员工的 name 及其 bonus。

展示效果：

name	bonus
John	null
Dan	500
Brad	null

Create table If Not Exists 17_Employee (EmpId int, Name varchar(255), Supervisor int, Salary int);
Create table If Not Exists 17_Bonus (EmpId int, Bonus int);
Truncate table 17_Employee;
insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (3, 'Brad', null, 4000);
insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (1, 'John', 3, 1000);
insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (2, 'Dan', 3, 2000);
insert into 17_Employee (EmpId, Name, Supervisor, Salary) values (4, 'Thomas', 3, 4000);
Truncate table 17_Bonus;
insert into 17_Bonus (EmpId, Bonus) values (2, 500);
insert into 17_Bonus (EmpId, Bonus) values (4, 2000);

最终SQL:

SELECT
    e.name, 
    b.bonus
FROM
    17_Employee e
LEFT JOIN
    17_Bonus b
ON 
    e.empid = b.empid
WHERE
    bonus < 1000 OR bonus IS NULL;

18. 最高回答率

需求：请编写SQL查询来找到具有最高回答率的问题。

展示效果：

survey_log
285

从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列**：id**, action, question_id, answer_id, q_num, timestamp。id 表示用户 id；action 有以下几种值：“show”，“answer”，“skip”；当 action 值为 “answer” 时 answer_id 非空，而 action 值为 “show” 或者 “skip” 时 answer_id 为空；q_num 表示当前会话中问题的编号。

Create table If Not Exists 18_survey_log (uid int, action varchar(255), question_id int, answer_id int, q_num int, timestamp int);
Truncate table 18_survey_log;
insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 285, null, 1, 123);
insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'answer', 285, 124124, 1, 124);
insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 369, null, 2, 125);
insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'skip', 369, null, 2, 126);

最终SQL:

-- 方法一
SELECT 
    question_id as survey_log
FROM
   (SELECT 
         question_id,
         SUM(case when action="answer" THEN 1 ELSE 0 END) as num_answer,
         SUM(case when action="show" THEN 1 ELSE 0 END) as num_show
	FROM 
         18_survey_log
	GROUP BY 
         question_id
    ) as tbl
ORDER BY
    (num_answer / num_show) DESC
LIMIT 1;

-- 方法二
SELECT 
    question_id AS 'survey_log'
FROM
    survey_log
GROUP BY
    question_id
ORDER BY
    COUNT(answer_id) / COUNT(IF(action = 'show', 1, 0)) DESC
LIMIT 1;

19. 员工累计薪水

需求：查询一个员工三个月内的累计薪水，但是不包括最近一个月的薪水。

展示效果：

Id	Month	Salary
1	3	90
1	2	50
1	1	20
2	1	20
3	3	100
3	2	40

Create table If Not Exists 19_Employee (Id int, Month int, Salary int);
Truncate table 19_Employee;
insert into 19_Employee (Id, Month, Salary) values (1, 1, 20);
insert into 19_Employee (Id, Month, Salary) values (2, 1, 20);
insert into 19_Employee (Id, Month, Salary) values (1, 2, 30);
insert into 19_Employee (Id, Month, Salary) values (2, 2, 30);
insert into 19_Employee (Id, Month, Salary) values (3, 2, 40);
insert into 19_Employee (Id, Month, Salary) values (1, 3, 40);
insert into 19_Employee (Id, Month, Salary) values (3, 3, 60);
insert into 19_Employee (Id, Month, Salary) values (1, 4, 60);
insert into 19_Employee (Id, Month, Salary) values (3, 4, 70);

说明：员工 1 除去最近一个月（月份 4），有三个月的薪水记录：月份 3 薪水为 40，月份 2 薪水为 30，月份 1 薪水为 20。所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

最终SQL:

SELECT
    E1.id,
    E1.month,
    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary
FROM
    (SELECT
        id, MAX(month) AS month
    FROM
        19_Employee
    GROUP BY 
        id
    HAVING 
        COUNT(*) > 1) AS maxmonth
    LEFT JOIN
        19_Employee E1 
    ON 
        (maxmonth.id = E1.id AND maxmonth.month > E1.month)
    LEFT JOIN
        19_Employee E2 
    ON 
        (E2.id = E1.id AND E2.month = E1.month - 1)
    LEFT JOIN 
        19_Employee E3 
    ON
        (E3.id = E1.id AND E3.month = E1.month - 2)
ORDER BY 
    id ASC , month DESC;

20. 统计各专业人数

需求：查询 department 表中每个专业的学生人数（即使没有学生的专业也需列出）。

展示效果：

dept_name	student_number
Engineering	2
Science	1
Law	0

CREATE TABLE IF NOT EXISTS 20_student (student_id INT,student_name VARCHAR(45), gender VARCHAR(6), dept_id INT);
CREATE TABLE IF NOT EXISTS 20_department (dept_id INT, dept_name VARCHAR(255));
Truncate table 20_student;
insert into 20_student (student_id, student_name, gender, dept_id) values (1, 'Jack', 'M', 1);
insert into 20_student (student_id, student_name, gender, dept_id) values (2, 'Jane', 'F', 1);
insert into 20_student (student_id, student_name, gender, dept_id) values (3, 'Mark', 'M', 2);
Truncate table 20_department;
insert into 20_department (dept_id, dept_name) values (1, 'Engineering');
insert into 20_department (dept_id, dept_name) values (2, 'Science');
insert into 20_department (dept_id, dept_name) values (3, 'Law');

最终SQL:

SELECT
    dept_name,
    COUNT(student_id) AS student_number
FROM
    20_department d
LEFT OUTER JOIN
    20_student s
ON
    d.dept_id = s.dept_id
GROUP BY 
    d.dept_name
ORDER BY 
    student_number DESC, 
    d.dept_name;

21. 寻找用户推荐人

需求：写一个查询语句，返回一个编号列表，列表中编号的推荐人的编号都不是 2

展示效果：

name
Will
Jane
Bill
Zack

CREATE TABLE IF NOT EXISTS 21_customer (id INT,name VARCHAR(25),referee_id INT);
Truncate table 21_customer;
insert into 21_customer (id, name, referee_id) values (1, 'Will', null);
insert into 21_customer (id, name, referee_id) values (2, 'Jane', null);
insert into 21_customer (id, name, referee_id) values (3, 'Alex', 2);
insert into 21_customer (id, name, referee_id) values (4, 'Bill', null);
insert into 21_customer (id, name, referee_id) values (5, 'Zack', 1);
insert into 21_customer (id, name, referee_id) values (6, 'Mark', 2);

最终SQL:

SELECT 
      name
FROM 
      21_customer
WHERE 
      referee_id <> 2 OR referee_id IS NULL;

22. 2016年的投资

需求：写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

展示效果：

TIV_2016
45.00

CREATE TABLE IF NOT EXISTS 22_insurance (PID INTEGER(11), TIV_2015 NUMERIC(15,2), TIV_2016 NUMERIC(15,2), LAT NUMERIC(5,2), LON NUMERIC(5,2) );
Truncate table 22_insurance;
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (1, 10, 5, 10, 10);
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (2, 20, 20, 20, 20);
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (3, 10, 30, 20, 20);
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (4, 10, 40, 40, 40);

提示：

对于一个投保人，他在 2016 年成功投资的条件是：

他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。

就如最后一个投保人，第一个投保人同时满足两个条件：

他在 2015 年的投保金额 TIV_2015 为 10 ，与第三个和第四个投保人在 2015 年的投保金额相同。
他所在城市的经纬度是独一无二的。

第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。

最终SQL:

SELECT
    SUM(i.TIV_2016) AS TIV_2016
FROM
    22_insurance as i
WHERE
    i.TIV_2015 IN(
                          SELECT
                                TIV_2015
                          FROM
                                22_insurance
                          GROUP BY 
                                TIV_2015
                          HAVING 
                                COUNT(*) > 1
                          )
AND 
    CONCAT(LAT, LON) IN(
                          SELECT
                                CONCAT(LAT, LON)
                          FROM
                                22_insurance
                          GROUP BY 
                                LAT , LON
                          HAVING COUNT(*) = 1
    );

23. 订单最多的客户

需求：在表 orders 中找到订单数最多客户对应的 customer_number 。

展示效果：

customer_number
3

Create table If Not Exists 23_orders (order_number int, customer_number int, order_date date, required_date date, shipped_date date, status char(15), comment char(200), key(order_number));
Truncate table 23_orders;
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (1, 1, '2017-04-09', '2017-04-13', '2017-04-12', 'Closed');
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (2, 2, '2017-04-15', '2017-04-20', '2017-04-18', 'Closed');
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (3, 3, '2017-04-16', '2017-04-25', '2017-04-20', 'Closed');
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (4, 3, '2017-04-18', '2017-04-28', '2017-04-25', 'Closed');

最终SQL:

SELECT
    customer_number
FROM
    23_orders
GROUP BY 
    customer_number
ORDER BY
    COUNT(*) DESC
LIMIT 1;

进阶：如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

24. 大的国家

需求：编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

展示效果：

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

Create table If Not Exists 24_World (name varchar(255), continent varchar(255), area int, population int, gdp bigint);
Truncate table 24_world;
insert into 24_World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', 652230, 25500100, 20343000000);
insert into 24_World (name, continent, area, population, gdp) values ('Albania', 'Europe', 28748, 2831741, 12960000000);
insert into 24_World (name, continent, area, population, gdp) values ('Algeria', 'Africa', 2381741, 37100000, 188681000000);
insert into 24_World (name, continent, area, population, gdp) values ('Andorra', 'Europe', 468, 78115, 3712000000);
insert into 24_World (name, continent, area, population, gdp) values ('Angola', 'Africa', 1246700, 20609294, 100990000000);

说明：如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

最终SQL:

-- 方法一：or
select 
      w.name,
      w.population,
      w.area
from 
      24_World w
where 
      w.area >3000000 or w.population >25000000

-- 方法二：union
select 
      w.name,
      w.population,
      w.area
from 
      24_World w
where 
      w.area>3000000
union
select
      w.name,
      w.population,
      w.area
from 
      24_World w
where 
      w.population>25000000

25. 超过五名学生的课

需求：编写一个 SQL 查询，列出所有超过或等于5名学生的课。

展示效果：

class
Math

Create table If Not Exists 25_courses (student varchar(255), class varchar(255));
Truncate table 25_courses;
insert into 25_courses (student, class) values ('A', 'Math');
insert into 25_courses (student, class) values ('B', 'English');
insert into 25_courses (student, class) values ('C', 'Math');
insert into 25_courses (student, class) values ('D', 'Biology');
insert into 25_courses (student, class) values ('E', 'Math');
insert into 25_courses (student, class) values ('F', 'Computer');
insert into 25_courses (student, class) values ('G', 'Math');
insert into 25_courses (student, class) values ('H', 'Math');
insert into 25_courses (student, class) values ('I', 'Math');

最终SQL:

select
      class 
from 
      25_courses 
group by 
      class
having 
      count(distinct student)>=5 ;

26. 好友申请

需求一：写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

展示效果：

accept_rate
0.80

Create table If Not Exists 26_friend_request ( sender_id INT NOT NULL, send_to_id INT NULL, request_date DATE NULL);
Create table If Not Exists 26_request_accepted ( requester_id INT NOT NULL, accepter_id INT NULL, accept_date DATE NULL);
Truncate table 26_friend_request;
insert into 26_friend_request (sender_id, send_to_id, request_date) values (1, 2, '2016/06/01');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (1, 3, '2016/06/01');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (1, 4, '2016/06/01');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (2, 3, '2016/06/02');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (3, 4, '2016/06/09');
Truncate table 26_request_accepted;
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (1, 2, '2016/06/03');
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (1, 3, '2016/06/08');
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (2, 3, '2016/06/08');
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (3, 4, '2016/06/09');

注意:

通过的好友申请不一定都在表 friend_request 中。在这种情况下，你只需要统计总的被通过的申请数（不管它们在不在原来的申请中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。

解释：总共有 5 个申请，其中 4 个是不重复且被通过的好友申请，所以成功率是 0.80 。

最终SQL:

select
      round(
            ifnull(
                   (select count(*) from (select distinct requester_id, accepter_id from 26_request_accepted) as A)
                   /
                   (select count(*) from (select distinct sender_id, send_to_id from 26_friend_request) as B)
            , 0)
      , 2) as accept_rate;

需求二：写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。

展示效果：

id	num
3	3

注意：

保证拥有最多好友数目的只有 1 个人。
好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。

最终SQL:

select 
     ids as id,
     cnt as num
from
    (select
           ids,
           count(*) as cnt
     from
           (select 
                  requester_id as ids 
            from
                  26_request_accepted
            union all
            select
                  accepter_id 
            from
                  26_request_accepted
            ) as tbl1
     group by ids
     ) as tbl2
order by 
     cnt desc
limit 1;

龍浮影

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第二部分14-26）

14. 至少有5名直接下属的经理需求：Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。展示效果：NameJohnCreate table If Not Exists 14_Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);Truncate table 14_Employee;insert into 14_Employee (Id, Name, D
复制链接

扫一扫