阅读文本大概需要 8 分钟。
近日，群里的朋友发了一道面试题，是关于不同行不同列的元素比较，很有料，我研究了好久才给出答案，如果是在面试现场，估计我就挂了，今天咱们就来复盘一下，它到底难在哪儿？ 面试题如下:（本文只研究第二题）
说不多说，先建表，建表语句如下：
代码语言：javascript复制 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
两表关联
代码语言：javascript复制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如何实现行转列？
代码语言：javascript复制 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查询结果如下：
加上过滤条件，得出答案
代码语言：javascript复制 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版本的窗口函数可以很好的实现组内排序，下面使用窗口函数结合子查询实现该查询
两表关联，并构造新的日期列
代码语言：javascript复制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')查询结果如下：
使用窗口函数添加同一职员的就职编号
代码语言：javascript复制 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有报错如下：
原因未知，经过调试，加上聚合后可以正常执行
代码语言：javascript复制 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'查询结果如下：
如上使用子查询嵌套，和窗口函数两种方法得出答案，每一种方法考察的知识点都很多，是一道很赞的面试题。
