一道很有料的MYSQL面试题

披头

发布于 2019-12-26 11:33:00

3710

发布于 2019-12-26 11:33:00

文章被收录于专栏：datartisan datartisandatartisan

阅读文本大概需要 8 分钟。

近日，群里的朋友发了一道面试题，是关于不同行不同列的元素比较，很有料，我研究了好久才给出答案，如果是在面试现场，估计我就挂了，今天咱们就来复盘一下，它到底难在哪儿？面试题如下:（本文只研究第二题）

说不多说，先建表，建表语句如下：

 1DROP TABLE IF EXISTS User_Employer;
 2CREATE TABLE User_Employer(
 3  User_id int,
 4  Employer_id int,
 5  Employment_Start_Date varchar(10),
 6  Employment_End_Date varchar(10));
 7
 8INSERT INTO User_Employer
 9VALUES
10  (1, 2, '2007-1-1',NULL),
11  (2, 1, '2016-12-4', '2018-1-2'),
12  (3, 3, '2017-6-21', '2018-1-4'),
13  (3, 2, '2018-1-5', NULL),
14  (2, 1, '2019-2-1', NULL),
15  (4, 2, '2016-11-4','2018-1-21');
16
17DROP TABLE IF EXISTS Employer;
18CREATE TABLE Employer(
19  ID int,
20  Employer_Name varchar(30));
21
22INSERT INTO Employer VALUES
23  (1, 'Google'),
24  (2, 'Realtor.com'),
25  (3, 'Zillow');

解法1

两表关联

1select *
2    from `user_employer` u
3    join `employer` e
4    on u.Employer_id = e.ID
5    WHERE e.Employer_Name in ('Realtor.com','Zillow')

查询结果如下：

构造新列，将用于计算的日期放到同一列

不同职员在不同公司的离职日期和入职日期位于不同行和不同列，难以比较大小，我们先将日期构造为一列，当就职单位为Zillow时我们需要的是离职日期，当就职单位为Realtor.com时我们需要的是入职日期，使用case when函数构造新列，代码如下：

1select *,
2    case  Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date end as cal_date
3    from `user_employer` u
4    join `employer` e
5    on u.Employer_id = e.ID
6    WHERE e.Employer_Name in ('Realtor.com','Zillow')

查询结果如下：

使用聚合函数实现行转列

具体可参见下文

mysql如何实现行转列？

 1select user_id,
 2    max(case Employer_Name when 'Realtor.com' then cal_date end) as Realtor_Start_Date,
 3    max(case Employer_Name when 'Zillow' then cal_date end) as Zillow_End_Date
 4    from (
 5            select user_id,Employer_Name,
 6                case  Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date end as cal_date
 7                from `user_employer` u
 8                join `employer` e
 9                on u.Employer_id = e.ID
10                WHERE e.Employer_Name in ('Realtor.com','Zillow') 
11              )tt
12group by user_id

查询结果如下：

加上过滤条件，得出答案

 1select user_id, Zillow_End_Date, Realtor_Start_Date
 2from 
 3(
 4    select user_id,
 5        max(case Employer_Name when 'Realtor.com' then cal_date end) as Realtor_Start_Date,
 6        max(case Employer_Name when 'Zillow' then cal_date end) as Zillow_End_Date
 7        from (
 8                select user_id,Employer_Name,
 9                    case  Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date end as cal_date
10                    from `user_employer` u
11                    join `employer` e
12                    on u.Employer_id = e.ID
13                    WHERE e.Employer_Name in ('Realtor.com','Zillow') 
14                  )tt
15    group by user_id)ttt
16WHERE Zillow_End_Date < Realtor_Start_Date

查询结果如下：

如上，使用三次子查询嵌套得出答案。

解法2

同一职员不同单位离职时间和入职时间的对比，本质上是组内排序，Mysql8.0版本的窗口函数可以很好的实现组内排序，下面使用窗口函数结合子查询实现该查询

两表关联，并构造新的日期列

1select *,
2    case Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date       end as cal_date
3from `User_Employer` u
4    inner join `Employer` e
5    on u.Employer_id = e.ID
6     WHERE e.Employer_Name in ('Realtor.com','Zillow')

查询结果如下：

使用窗口函数添加同一职员的就职编号

 1SELECT *, 
 2  row_number() OVER (PARTITION BY user_id 
 3                     ORDER BY cal_date) AS myrank
 4  FROM
 5
 6      (
 7        select *,
 8      case Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date         end as cal_date
 9      from `User_Employer` u
10      inner join `Employer` e
11      on u.Employer_id = e.ID
12      WHERE e.Employer_Name in ('Realtor.com','Zillow')
13      )tt

有报错如下：

原因未知，经过调试，加上聚合后可以正常执行

 1SELECT *, 
 2  row_number() OVER (PARTITION BY user_id 
 3                     ORDER BY cal_date) AS myrank
 4  FROM
 5
 6      (
 7        select *,
 8      max(case Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date         end) as cal_date
 9      from `User_Employer` u
10      inner join `Employer` e
11      on u.Employer_id = e.ID
12      WHERE e.Employer_Name in ('Realtor.com','Zillow')
13        group by User_id,Employer_id,Employment_Start_Date,Employment_End_Date,ID,Employer_Name
14      )tt

查询结果如下：

加上过滤条件，得出答案

当排序编号为2，且就职单位是Realtor.com就是满足条件的答案

 1SELECT * FROM
 2  (
 3  SELECT *, 
 4  row_number() OVER (PARTITION BY user_id 
 5                     ORDER BY cal_date) AS myrank
 6  FROM
 7
 8      (
 9        select *,
10      max(case Employer_Name when 'Realtor.com' then Employment_Start_Date else Employment_End_Date         end) as cal_date
11      from `User_Employer` u
12      inner join `Employer` e
13      on u.Employer_id = e.ID
14      WHERE e.Employer_Name in ('Realtor.com','Zillow')
15        group by User_id,Employer_id,Employment_Start_Date,Employment_End_Date,ID,Employer_Name
16      )tt
17    )ttt
18    where myrank = 2 and Employer_Name = 'Realtor.com'

查询结果如下：