一对儿程序猿 2019-12-08
我们现在有一张表titles,共有4个字段,分别是emp_no(员工编号),title(职位),from_date(起始时间),to_date(结束时间),记录的是员工在某个时间段内职位名称,因为会存在升职,转岗之类的,里面emp_no可能会对应多个职位,我们现在要取到所有员工最近的职位信息,包括离职员工。
本文介绍两种方法去实现结果:
嵌套一个group by+max()子查询获取最近的职位信息。
SELECT emp_no, max( from_date ) AS max_date FROM titles GROUP BY emp_no
结果如下:
SELECT t.emp_no, t.title FROM titles t LEFT JOIN ( SELECT emp_no, max( from_date ) AS max_date FROM titles GROUP BY emp_no ) et ON t.emp_no = et.emp_no AND t.from_date = et.max_date
结果如下:
通过rank over partition by函数实现,这个目前是Oracle独有的函数,如果你用的是mysql或者sql server就没办法使用了。
功能:在原有表的基础上加上一个根据条件排序的伪列。
SELECT *, RANK() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS rank FROM titles
RANK() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS rank
表示把表根据emp_no进行分区,然后在分区内根据from_date进行降序排列,排序结果生成一列命名为rank。
我们之前在问题里面提到了一个emp_no会对应多条职位信息,然后对于每个emp_no的记录进行一个降序排列,接下来我们只需要把上面的结果当成一个子查询然后筛选rank = 1
就好了。
SELECT * FROM ( SELECT *, RANK ( ) OVER ( PARTITION BY emp_no ORDER BY from_date DESC ) AS rank FROM titles ) r WHERE r.rank = '1'
由于我笔记本只装了mysql的环境,所以就没法给各位展示效果了。
综上,如果各位目前使用的是Oracle,推荐各位使用方法二:
peace~