I was giving a few thoughts to the idea, and I think I found the solution: Use user variables:
- The first thing I did was sort by
id
ascending, and by fecha
in a way descending , to be able to "drag" the value of fecha
.
- Then, I used a small trick with temporary variables: Since when values are assigned to user variables in a query they are updated in each row , then it is possible to reference their previous value < em> before updating them :
- An expression
case
verifies if the id
of the current record is the same as the id
of the previous record (which is in the variable @id_ant
):
- If the value of
id
is equal to the value of @id_ant
, then put the value of @f_ini
(which is updated in each row with the value of fecha
);
- If the value of
id
no is equal to the value of @id_ant
, then put a NULL
.
- Next, update the values of
@id_ant
and @f_ini
, to be able to use them in the next row.
The result was like this (I used [SQL Fiddle] [ link to make the problem reproducible, I added some more values so that the example was clearer):
SQL Fiddle
MySQL 5.6 Schema Setup :
create table prueba (
id int unsigned not null,
fecha datetime
);
insert into prueba values
(10, '2016-12-01 10:15:00'),
(10, '2016-12-03 16:02:00'),
(10, '2016-12-05 19:19:00'),
(11, '2016-12-02 11:23:00'),
(11, '2016-12-04 22:00:00'),
(12, '2016-12-05 02:00:00');
Query 1 :
-- Contenido original de la tabla:
select * from prueba;
Results :
| id | fecha |
|----|---------------------|
| 10 | 2016-12-01 10:15:00 |
| 10 | 2016-12-03 16:02:00 |
| 10 | 2016-12-05 19:19:00 |
| 11 | 2016-12-02 11:23:00 |
| 11 | 2016-12-04 22:00:00 |
| 12 | 2016-12-05 02:00:00 |
Query 2 :
-- Consulta original:
select case when @id_ant = id then @f_ini else null end as f_fin
-- Actualiza el Id y la fecha de inicio
, @id_ant := id as id
, @f_ini := fecha as f_ini
from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
order by id asc, fecha desc;
Results :
| f_fin | id | f_ini |
|---------------------|----|---------------------|
| (null) | 10 | 2016-12-05 19:19:00 |
| 2016-12-05 19:19:00 | 10 | 2016-12-03 16:02:00 |
| 2016-12-03 16:02:00 | 10 | 2016-12-01 10:15:00 |
| (null) | 11 | 2016-12-04 22:00:00 |
| 2016-12-04 22:00:00 | 11 | 2016-12-02 11:23:00 |
| (null) | 12 | 2016-12-05 02:00:00 |
Query 3 :
-- Consulta con los campos y los valores en el orden correcto:
select id, f_ini, f_fin
from (
select case when @id_ant = id then @f_ini else null end as f_fin
-- Actualiza el Id y la fecha de inicio
, @id_ant := id as id
, @f_ini := fecha as f_ini
from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
order by id asc, fecha desc
) as a
order by id, f_ini
Results :
| id | f_ini | f_fin |
|----|---------------------|---------------------|
| 10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00 |
| 10 | 2016-12-03 16:02:00 | 2016-12-05 19:19:00 |
| 10 | 2016-12-05 19:19:00 | (null) |
| 11 | 2016-12-02 11:23:00 | 2016-12-04 22:00:00 |
| 11 | 2016-12-04 22:00:00 | (null) |
| 12 | 2016-12-05 02:00:00 | (null) |