MySQL Group按连续值 [英] MySQL Group by consecutive values

问题描述

我有rate_history表，其中包含每位员工的数据及其在工作岗位上的费率变化.我想在单个查询中获取每个员工的职位变动.选定的作业是在连续的行中具有最新更新的作业.请注意，随着时间的推移，可以将同一职位重新分配给员工.

I have rate_history table, with data for each employee and his rate changes over job positions. I want to get job position changes for each employee in single query. Selected job is one with most current update in consecutive rows. Note, same job position can be re assigned to an employee over time.

id job_id updated
1    1    01-01-2015
2    1    01-02-2015
3    2    01-01-2015
4    2    01-03-2015
5    2    01-02-2015
6    1    01-01-2015

结果应为:

id job_id updated
1    1    01-01-2015
3    2    01-01-2015
6    1    01-02-2015

推荐答案

好的，这是我对您提供的输出的理解基础上的小提琴链接，

okay, here is my fiddle link based on my understanding on the output you provided,

您可以使用行号(不适用于mysql，但可以使用环形交叉路口)，然后获取具有job_ids与以前的记录不同的记录.

You can use row number(not available with mysql but using roundabout) and then getting records which are having job_ids not same as previous records.

小提琴链接

select t1.id
     , t1.job_id
     , t1.updatedate
     , t2.rn as rnt2
  from temprwithrn as t1
  left 
  join temprwithrn as t2
    on t1.rn = t2.rn + 1
 where t1.job_id <> t2.job_id or t2.job_id is null

这篇关于MySQL Group按连续值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！