Ordenar Valores de Columnas, MySQL

Ordenar Valores de Columnas

create table Ejemplo (
  ID    INT,
  TOTAL_M_01  INT,
  TOTAL_M_02  INT,
  TOTAL_M_03  INT,
  O_01 INT,
  O_02 INT,
  O_03 INT
);

INSERT INTO Ejemplo (ID, TOTAL_M_01, TOTAL_M_02, TOTAL_M_03) values (1,3,5,1);
INSERT INTO Ejemplo (ID, TOTAL_M_01, TOTAL_M_02, TOTAL_M_03) values (2,2,6,7);
INSERT INTO Ejemplo (ID, TOTAL_M_01, TOTAL_M_02, TOTAL_M_03) values (3,6,1,3);

CREATE TEMPORARY TABLE IF NOT EXISTS Temporal AS (
SELECT ID, 
       GROUP_CONCAT(Columnas order by Columnas DESC) ColumnasOrdenadas
       FROM ( SELECT  ID,
                   (case when n = 1 then TOTAL_M_01
                         when n = 2 then TOTAL_M_02
                         when n = 3 then TOTAL_M_03
                    end
                   ) as Columnas
        from Ejemplo a 
        cross join (select 1 as n union all select 2 union all select 3) n
    ) T
    GROUP BY ID
);

select * from Temporal;

UPDATE Ejemplo E
       INNER JOIN Temporal T
           ON E.ID = T.ID
       SET O_01 = CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(T.ColumnasOrdenadas, ',', 1), ',', -1),UNSIGNED INTEGER),
           O_02 = CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(T.ColumnasOrdenadas, ',', 2), ',', -1),UNSIGNED INTEGER),
           O_03 = CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(T.ColumnasOrdenadas, ',', 3), ',', -1),UNSIGNED INTEGER);

SELECT  *
        FROM Ejemplo;

DROP TABLE Ejemplo;
DROP TABLE Temporal;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development