SQL查询出每个项目中经验最丰富的员工

一、题目

​ 有如下两张表：

​ T1201A表：

​ T1201B表：

​

​ 查询出每个项目组中经验最丰富（即experience_years最大）的员工，返回结果如下：

​ 说明：员工1和员工3均为project_id为1中experience_years最丰富的员工，因为他们的experience_years相等，都为项目1中experience_years最大的。

二、测试数据

​ 为了方便大家测试，SQL建表语句如下：

CREATE TABLE `t1201a` (
  `project_id` int NOT NULL,
  `employee_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO T1201A VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(2,4);

CREATE TABLE `t1201b` (
  `employee_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `experience_years` int DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO T1201B VALUES
(1,'张三',3),
(2,'李四',2),
(3,'王五',3),
(4,'马六',2);

三、思路讲解

1. 首先分析题目，最后需要输出每个项目组experience_years最大的员工，而且根据题目要求，可能存在多个员工的experience_years一样，所以应该是一个并列排名，此时可以想到已知的函数DENSE_RANK()可以满足这个排名的需求；并且是要按照project_id进行分组的，每个项目都要找出experience_years最大的员工，将experience_years进行倒序排序。根据目前的分析，我可以写出如下SQL：

   SELECT DENSE_RANK() over(PARTITION BY project_id ORDER BY experience_years DESC) myrank
   FROM ?;
   

2. 可以看到步骤1中SQL的project_id和experience_years显然不是一个表数据，需要的是T1201A表和T1201B表中综合的数据，可以想到需要将两个表join起来，这样就可以拿到两个表里全部的数据了。因此，可以将步骤1中SQL进行完善，写出如下SQL：

   SELECT DENSE_RANK() over(PARTITION BY s.project_id ORDER BY s.experience_years DESC ) myrank,s.* 
   FROM(
       SELECT a.project_id, a.employee_id, b.experience_years 
    	FROM t1201a a 
       JOIN t1201b b 
       ON a.employee_id = b.employee_id 
   ) s
   

   运行此SQL，查询出的结果为：

   

3. 可以看到现在写出的SQL已经初步满足了需求，按照project_id进行了分组排名，不过和最终需求还是有一些差距的，最终需求只要求显示每组的第一名，现在显示了每组的所有排名，那么，我们就可以继续完善SQL，如下所示：

   SELECT
   	t.project_id,
   	t.employee_id 
   FROM
   	(
   	SELECT
   		DENSE_RANK() over ( PARTITION BY s.project_id ORDER BY s.experience_years DESC ) myrank,
   		s.* 
   	FROM
   		( SELECT a.project_id, a.employee_id, b.experience_years FROM t1201a a JOIN t1201b b ON a.employee_id = b.employee_id ) s 
   	) t 
   WHERE
   	t.myrank = 1
   

   运行此SQL，结果如下所示：

   

   可以看到，最终运行结果，已经符合了需求。

四、优化

我们的任务虽然是完成需求，但是对于SQL的优化也是必不可少的，以上只是为了完成需求不断思考的过程，这样得到的SQL不一定是最好的SQL，可以看得到完成的SQL嵌套了两个子查询。分析SQL发现，最内层的子查询所做的事情只是将两个表的数据联合起来，那么是不是可以进行优化呢？

最终优化的SQL如下所示：

SELECT
	t.project_id,
	t.employee_id 
FROM
	(
	SELECT
		DENSE_RANK() over ( PARTITION BY a.project_id ORDER BY b.experience_years DESC ) myrank,a.*,b.experience_years
	FROM
		t1201a a
		JOIN t1201b b 
		ON a.employee_id = b.employee_id 
	) t 
WHERE
	t.myrank = 1

将SQL进行验证，结果正确。

当然，对于SQL查询来说，只要满足需求，怎么样实现都可以的，毕竟一千个人心里有一千个哈姆雷特，爱咋做咋做，这只是我的想法而已，just do it！！！