Lanaldiak, MySQL - rextester

Lanaldiak

create temporary table lan (CDNI text, DFECALTA text, DFECBAJ text);
insert into lan values
('2273086','enero','agosto'),
('2273086','febrero', 'marzo'),
('111111','enero', 'julio'),
('111111','febrero', 'agosto'),
('222222','julio', 'setiembre'),
('222222','agosto', 'octubre'),
('2273086','agosto', 'diciembre');

create temporary table lanaldiak (cid int, CDNI text, DFECALTA text, DFECBAJ text);

INSERT INTO lanaldiak(cid, CDNI, DFECALTA , DFECBAJ)

select 
       @cid := case when @CDNI = CDNI then @cid + 1 else @cid := 1 end as cid,
       @CDNI := CDNI as CDNI, DFECALTA, DFECBAJ
from
    (select @cid := 1) i,
    (select @CDNI := CDNI as CDNI, DFECALTA, DFECBAJ from lan order by CDNI,DFECALTA,DFECBAJ) t;

create temporary table lanaldiak2 (cid2 int, CDNI2 text, DFECALTA2 text, DFECBAJ2 text);

INSERT INTO lanaldiak2(cid2, CDNI2, DFECALTA2 , DFECBAJ2)

select 
       @cid := case when @CDNI = CDNI then @cid + 1 else @cid := 0 end as cid,
       @CDNI := CDNI as CDNI, DFECALTA, DFECBAJ
from
    (select @cid := 0) i,
    (select @CDNI := CDNI as CDNI, DFECALTA, DFECBAJ from lan order by CDNI,DFECALTA,DFECBAJ) t;
    
    
SELECT lanaldiak.cid, lanaldiak.CDNI, lanaldiak.DFECALTA, lanaldiak.DFECBAJ, lanaldiak2.DFECALTA2, lanaldiak2.DFECBAJ2
FROM lanaldiak INNER JOIN lanaldiak2 ON lanaldiak.CDNI = lanaldiak2.CDNI2 and lanaldiak.cid = lanaldiak2.cid2;


run \| edit \| history \| help	0

Please log in to post a comment.